Parent/Child relationship with multiple child resuts

Lemonboi

I've come up against a wall for myself. Want to improve my inputs for users and give them the ability to update numbers on their list, however I need to ensure I keep the past information.

I've created a Parent Table which has standard information. In this case the parent table has columns for debtor, amount, repayment amount. On the child, I have the interest rate which relates back to the parent with the key of debt_id

My issue is that I want to create a table that shows the current conditions of the debt. So I need to put on that table, debtor, the amount, repayment amount and current interest rate.

Parent Table
debt_id
id_user
debtor
amount
repayment

Child
debt_id
rate
date (date the new rate is registered)

so whilst a single debtor can have many rates attached to it, but I need the latest interest rate applied to it. I need to retain the previous line entries for calculating applicable interest rates in the past.

johanafm

The only thing you need to do is add a new column id to child and set this as the primary key. You should also drop any uniqueness constraint on debt_id.

Lemonboi

This is the SQL I used prior, but at the moment, if there are multiple lines for interest rate in the child, it brings up multiple results for the same debt with the different interest rates

SELECT T2.interest_id, 
                 T1.debt_id AS d_id,
		 T1.id_user,
		 T1.debtor AS debtor,
		 T1.amount AS amount,
		 T1.repayment as repayment,
		 T2.interest AS ir
	 FROM Parent AS T1 
	INNER JOIN Child AS T2 
		ON T1.debt_id=T2.debt_id 
	WHERE T1.id_user='8'
	ORDER BY T2.interest DESC

ie it'll list:
50,56,8,Debtor A,1000,50,10.5
54,56,8,Debtor A,1000,50,10.0
53,52,8,Debtor B,5000,100,7.0

even though the date/interest_id of entering 10.0 is after 10.5

I only have a key set on interest_id in the Child table.

Weedpacket

Order by date (descending) and limit the results to one.

Lemonboi

Came up with a solution that required a few subqueries

SELECT T1.debt_id AS debt_id,
		 T1.userid,
		 T1.debtor AS debtor,
		 T1.amount AS amount,
		 T1.repayment as repayment,
		 T1.start_amount AS start_amount,
		 T2.interest AS ir,
		 T2.date AS date
	 FROM debtslist AS T1 
	INNER JOIN (SELECT date, di_id, interest, debt_id
	FROM debt_interest
	WHERE debt_id IN
		(SELECT debt_id
	FROM debtslist
	WHERE userid='9')
	ORDER BY date DESC
	) AS T2 
		ON T1.debt_id=T2.debt_id 
	WHERE userid='9'
	GROUP BY debt_id 
	ORDER BY T2.interest DESC

This selects all the debts for a given user.

Maybe there's a better way of doing what i've done which is to select all the debts for a user, then using that in the where clause to select the latest interest rates for those debts, then in the last selecting the debt information to finish.