左外连接问题 [英] left outer join question

问题描述

我有三个表来管理不同的社交媒体帐户-facebook，twitter和linkedin.以下查询将返回Facebook帐户正常，但没有其他内容-我在做什么错呢?每个表都是通过user_id字段链接的，但是每个记录也都有自己的id字段.

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

推荐答案

查询的问题是，如果该用户没有Facebook帐户，那么您将不会获得有关该用户的任何信息.如果用户没有linkedin帐户，那么您将不会获得有关其Twitter帐户的信息. LEFT JOINS不适合此目的，但是MySQL不支持FULL OUTER JOINS，因此，作为一种解决方法，您可以执行以下操作:

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

这篇关于左外连接问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！