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开发者_JS百科 
    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

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

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

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)

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

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

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';