CODEWITHSUNDEEP
mysqlsql
Chris Mccabe
Chris Mccabe

Reputation: 1951

left outer join question

I have three tables than manage different social media accounts- facebook, twitter and linkedin. the following query will return facebook accounts fine but nothing else- what am i do doing wrong? each table is linked via a user_id field but every record has its own id fiel as well.

SELECT 
  bla_facebook_accts.id as facebook, 
  bla_linked_in_accts.id as linkedin, 
  bla_twitter_accts.id as twitter  
FROM
  bla_facebook_accts 
  LEFT OUTER JOIN
    bla_linked_in_accts 
    ON
      (bla_linked_in_accts.user_id = bla_facebook_accts.user_id)
  LEFT OUTER JOIN 
    bla_twitter_accts
    ON
      (bla_linked_in_accts.user_id = bla_twitter_accts.user_id) 
WHERE bla_facebook_accts.user_id = '12';

any ideas where im going wrong- i simply want it to return a row for if the user has an account on each network- ie- record in each table

Upvotes: 1

Views: 189

Answers (6)

Ivar Bonsaksen
Ivar Bonsaksen

Reputation: 4767

If you want to list all existing accounts for a given user, regardless of how many accounts that user have, LEFT JOIN (aka LEFT OUTER JOIN) is the correct way to go, but you must join on something you know will exist for all users. Therefore you should base your query on your user table, and join all the tables on the user_id in the user table.

Rename userTable to whatever your user table is called

SELECT
    facebook.id as facebook,
    linkedin.id as linkedin,
    twitter.id as twitter
FROM
    userTable
LEFT JOIN
    bla_facebook_accts facebook ON (userTable.user_id = facebook.user_id)
LEFT JOIN
    bla_linked_in_accts linkedin ON (userTable.user_id = linkedin.user_id)
LEFT JOIN
    bla_twitter_accts twitter ON (userTable.user_id = twitter.user_id)
WHERE
    userTable.user_id = 12

Upvotes: 1

Karolis
Karolis

Reputation: 9562

The problem with your query is that if the user has no facebook account then you will not get any information about this user. And if the user has no linkedin account then you will not get information about its twitter account. LEFT JOINS are not suitable for this purpose, but MySQL does not support FULL OUTER JOINS, so as a workaround you can do something like this:

select
    (select id from bla_facebook_accts where user_id = user.user_id) as facebook,
    (select id from bla_linked_in_accts where user_id = user.user_id) as linkedin,
    (select id from bla_twitter_accts where user_id = user.user_id) as twitter
from
    (select 12 user_id) user

Upvotes: 2

StevieG
StevieG

Reputation: 8709

You'll never get an answer if you try to do the second join using the linkedin id if your user doesn't have a linkedin account but does have a facebook account. You need to alwaysjoin using your source account (facebook)

SELECT 
  bla_facebook_accts.id as facebook, 
  bla_linked_in_accts.id as linkedin, 
  bla_twitter_accts.id as twitter  
FROM
  bla_facebook_accts 
  LEFT OUTER JOIN
    bla_linked_in_accts 
    ON
      (bla_linked_in_accts.user_id = bla_facebook_accts.user_id)
  LEFT OUTER JOIN 
    bla_twitter_accts
    ON
      (bla_linked_in_accts.user_id = bla_facebook_accts.user_id) 
WHERE bla_facebook_accts.user_id = '12';

Upvotes: 0

cEz
cEz

Reputation: 5062

Following the comment about no FB account:

(SELECT 'Facebook' as 'ac', id FROM bla_facebook_accts WHERE user_id=12)
UNION
(SELECT 'LinkedIn' as 'ac', id FROM bla_linked_in_accts WHERE user_id=12)
UNION
(SELECT 'Twitter' as 'ac', id FROM bla_twitter_accts WHERE user_id=12)

Upvotes: 1

Rolando Cruz
Rolando Cruz

Reputation: 2784

I think you want to use INNER JOINs if you want to return a row that has a record in all tables.

SELECT
  f.id as facebook, 
  l.id as linkedin, 
  t.id as twitter
FROM
  bla_facebook_accts f
  INNER JOIN bla_linked_in_acctsn l ON f.user_id = l.user_id 
  INNER JOIN bla_twitter_accts t ON f.user_id = t.user_id

Upvotes: 1

Volker Pacher
Volker Pacher

Reputation: 1877

try:

Select t1.column, t2.column, t2.column from table left outer join (t1,t2,t3) on (t1.user_id = table.user_id AND t2.user_id = table.user_id AND t3.user_id = table.user_id)

etc

Upvotes: 0

Related Questions