CODEWITHSUNDEEP
mysql
Dan Hastings
Dan Hastings

Reputation: 3280

SQL Triple Join Where Some Rows In A Table May Not Exist

I have 1 table that contains the main rows and i want all of these rows. Then there are 2 tables that may not have a matching row, but i still want the row from the main table if there is no matching row for these tables.

This query works, but if there is not a row in all 3 tables nothing gets returned. Im using mysql with php.

SELECT PS_Info.* , PS_Guides.Guide, PS_User.UserID FROM PS_Info 
JOIN PS_Guides ON PS_Info.ID = PS_Guides.InfoID 
JOIN PS_User ON PS_Info.ID = PS_User.InfoID 
WHERE PS_Info.GameID = :ID AND PS_User.UserID = :UserID

I am trying to display a list which is in PS_Info. Then i want to hide some of these items if the user has them set to hidden (PS_User = UserID, InfoID). If there is nothing in PS_User then they have not set this item to be hidden so i want to display it. PS_Guides contains extra info about each of the items in the list. Not all items contain extra info so if there is no row in this table then i dont display it.

Upvotes: 0

Views: 59

Answers (2)

Dhaval
Dhaval

Reputation: 2379

SELECT PS_Info.* , PS_Guides.Guide, PS_User.UserID FROM PS_Info 
left JOIN PS_Guides ON PS_Info.ID = PS_Guides.InfoID //use left join 
left JOIN PS_User ON PS_Info.ID = PS_User.InfoID   //use left join 
WHERE PS_Info.GameID = :ID AND PS_User.UserID = :UserID

Upvotes: 2

Lorenz Meyer
Lorenz Meyer

Reputation: 19895

You are looking for 'left join'. 

SELECT PS_Info.* , PS_Guides.Guide, PS_User.UserID   FROM PS_Info 
LEFT JOIN PS_Guides ON PS_Info.ID = PS_Guides.InfoID 
LEFT JOIN PS_User ON PS_Info.ID = PS_User.InfoID 
WHERE PS_Info.GameID = :ID AND PS_User.UserID = :UserID

Upvotes: 2

Related Questions