multiple join query

[andydeans]

hi,

i have been trying to write a query which will return all clients and the related tables so that i can then allow the users to filter in a php website.

at moment i tried this but returned no results even though there are records:

SELECT *
FROM clients JOIN buytolet on clients.ClientID = buytolet.clients_ClientID JOIN protection on clients.ClientID = protection.clients_ClientID

to further the query i need to join lenders name to the protection table so they can filter by lender name etc, however i just want to create the joins so i can then create the filter.

can anyone help please?

many thanks

[johny_d]

Are you sure you have records that match all the joins at the same time? Cause there is nothing wrong with the query.
Try using LEFT JOIN instead of JOIN and see what it returns.
This will return all the rows in your clients table and either fields with data (for matching rows) or empty fields for non matching rows, for the other tables.

[andydeans]

hi,

thanks for your reply.

i does work but only if there is a record in each. i want to show the clients table regardless of having a record in any of the other tables.

would that be a left join?

thanks again for your help

[johny_d]

Yes, that is the LEFT JOIN: it returns all the records from the first table that match the conditions of the query, joined with the coresponding rows from the other table(s) if there are any or with NULL values instead where there are no coresponding rows.