Help with double join

[andydeans]

Hi,

I have tested this and get no errors.

However when i am updating a mortgage record for a client it changes the client id??

So if i am updating a mortgage for clientid = 1 Test Case and Mortgage ID = 1 once i click update in my php site i then get redircted to the details page and it shows clientid=1 Another Test Name ??

here is my query:

SELECT m.MortgageID, m.AccountNumber, m.Lender, m.LoanAmount, m.Term, m.PurchasePrice, m.ReviewDate, m.DatePaid, m.DateSubmitted, m.StartDate, m.Amount, m.RepaymentType, m.clients_ClientID, m.IssueDateMortgage, m.MortgageTypeName, m.MortgageSold, m.MortgageStatus, c.ClientID, c.App1FirstName, c.App1LastName, c.App2FirstName, c.App2LastName, c.ClientUserID, p.Pip_CommID, p.clients_ClientID
FROM mortgage m Join clients c on m.clients_ClientID = c.ClientID JOIN pipeline_commission p on p.clients_ClientID
WHERE MortgageID = ParamMortgageID

not sure if my join is wrong?

many thanks

[tyson.crouch]

with your second join, what is p.clients_ClientID referring to?

SELECT m.MortgageID, m.AccountNumber, m.Lender, m.LoanAmount, m.Term, m.PurchasePrice, m.ReviewDate, m.DatePaid, m.DateSubmitted, m.StartDate, m.Amount, m.RepaymentType, m.clients_ClientID, m.IssueDateMortgage, m.MortgageTypeName, m.MortgageSold, m.MortgageStatus, c.ClientID, c.App1FirstName, c.App1LastName, c.App2FirstName, c.App2LastName, c.ClientUserID, p.Pip_CommID, p.clients_ClientID
FROM (mortgage m INNER JOIN clients c ON m.clients_ClientID = c.ClientID )
INNER JOIN pipeline_commission p ON p.clients_ClientID = m.clients_ClientID
WHERE MortgageID = ParamMortgageID

I've cleaned this up a little, just used parenthesis to segment the joins. Hope it helps, even a little

[andydeans]

thanks, i am having real problems with my insert into table.

If anyone out there could help please.

Basically i have my insert page which adds a product to the product table, on the fly it inserts into the commission table.

this works fine,

i have then went into my update page and added an update record on the fly to update the commission table. Only thing is it adds a new entry and does not update the existing ID Record.
can anyone help??

thanks

[tyson.crouch]

Could you please post up your table structure in the form of the create query??

Also, please post up your UPDATE queries for both the product table as well as the commission table so that we can help you better.

Thx
TC

[andydeans]

thanks for your help, after looking at my pages i think the problem was i was not setting the clientid to the appropriate query so once i changed this it now works.

thanks for your help,
p.s. hope you can look at my other post as i need some help with some queries.

cheers

[tyson.crouch]

if your problem is solved. Please mark thread as Solved so that it is recognized as closed.

[andydeans]

Ok thanks, again for your help.