MySQL: Retrieve data from multiple tables

Question

Please have a look at the below table structure.

Client table has the foreign key for Provider Table, which is not NULL. Portfolio table has the foreign key for the Client table, which is also not NULL.

I need to retrieve all the fields from the Portfolio table, Name of the Client and the Provider Name who is allocated to the Client which is referred by the Portfolio table..

How can I do this in SQL Code?

Answer 1

Try following query with INNER JOIN.

SELECT Portfolio.*,Client.name as "Client Name",Provider.name as "Provider Name"
FROM Portfolio
INNER JOIN Client ON Portfolio.Client_id=Client.id
INNER JOIN Provider ON Client.Provider_id = Provider.id

Answer 2

This should give you the result you expected:

Select * from client 
  join portfolio on client.id=portfolio.clientId
  join provider on client.provider_id=provider.id