mysql Full outer join two table-CodePudding

I have two tables socials , social_links and I want all rows from frist table and related rows from second table

my code

SELECT socials.*,social_links.*
from socials     left join  social_links  on socials.id=social_links.social_id  
where social_links.site_id=1

this code return 2 rows but I want 4 rows where site_id=1

frist table socials

id , name        
1   Facebook
2   Instagram
3   Linkedin
4   Messenger

Second table social_links

id , social_id , site_id , link
1       1           1       https://www.facebook.com/lolo
2       2           1       https://instagram.com/test
3       1           2       https://www.facebook.com/koko

I want all rows from socials join with social_links where site_id=1

CodePudding user response：

instead of where add your condition in the join

SELECT socials.*,social_links.*
from socials     left join  social_links  on socials.id=social_links.social_id and social_links.site_id=1