MySQL multiple left joins on one table

Question

I have a database with companies and domains. My goal is to link all social media channels to the domain of every country

SELECT 
  yt.screen_name,
  tw.screen_name,
  gp.id,
  fb.title,
  dom.domain_name,
  dom.type,
  dom.url
FROM
  domain AS dom1
  LEFT JOIN youtube AS yt
    ON yt.company_name = dom1.company_name 
    AND domain AS dom2
  LEFT JOIN twitter AS tw
    ON tw.company_name = dom2.company_name 
    AND domain AS dom3
  LEFT JOIN googleplus AS gp
    ON gp.company_name = dom3.company_name 
    AND domain AS dom4
  LEFT JOIN facebook AS fb
    ON fb.company_name = dom4.company_name 
WHERE 
  yt.screen_name IS NOT NULL
  OR tw.screen_name IS NOT NULL
  OR gp.id IS NOT NULL
  OR fb.title IS NOT NULL
ORDER BY dom.url

my goal is to get a table like this:

yt.screen_name | tw.screen_name | gp.id | fb.title | dom.domain_name | dom.type | dom.url
foo             NULL             NULL    NULL       foo.com           website    http://www.foo.com
NULL            foo              NULL    NULL       foo.com           website    http://www.foo.com 
NULL            NULL             0478174 NULL       foo.com           website    http://www.foo.com

is this possible?

company_name is a bad thing to join on as 2 companies could have the same name and the name could change. It's better to have a company_id or similar field. — diolemo
– diolemo, Commented Jan 22, 2015 at 15:19
Can you post a sample from the domain table and update your output example to show what it would look like for more than one domain? If I understand the output, it may be a UNION chain you're after with NULL literals. — Michael Berkowski
– Michael Berkowski, Commented Jan 22, 2015 at 15:22
You cannot do this: AND domain AS dom2 and dom is not defined. Try defining domain once, and compare everything to that instance only, like LEFT JOIN facebook AS fb ON fb.company_name = dom.company_name. — JokiRuiz
– JokiRuiz, Commented Jan 22, 2015 at 15:27

SouravA · Accepted Answer · 2015-01-22 15:51:07Z

2

Try this

SELECT DISTINCT
  yt.screen_name,
  tw.screen_name,
  gp.id,
  fb.title,
  dom1.domain_name,
  dom1.type,
  dom1.url
FROM
  domain AS dom1
  LEFT JOIN youtube AS yt
    ON yt.company_name = dom1.company_name 

  LEFT JOIN twitter AS tw
    ON tw.company_name = dom1.company_name 

  LEFT JOIN googleplus AS gp
    ON gp.company_name = dom1.company_name 

  LEFT JOIN facebook AS fb
    ON fb.company_name = dom1.company_name 
WHERE 
  yt.screen_name IS NOT NULL
  OR tw.screen_name IS NOT NULL
  OR gp.id IS NOT NULL
  OR fb.title IS NOT NULL
ORDER BY dom1.url

edited Jan 22, 2015 at 15:51

answered Jan 22, 2015 at 15:21

SouravA

5,2632 gold badges30 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

CTSchmidt Over a year ago

okay the syntax error is gone, but i get strange table with all social media channels are mixed together. imagine my table above -> NULL is gone and there are the names up to a point where the first value switch

SouravA Over a year ago

You mean NULL is not appearing at all?

CTSchmidt Over a year ago

Yepp, it will be filled with duplicated stuff

SouravA Over a year ago

You probably need distinct records. You need to add DISTINCT at the top. Check the edited code.

SouravA Over a year ago

If that didn't work, you need to rethink the or conditions in 'where' clause

Evan Volgas · Accepted Answer · 2015-01-22 15:21:14Z

1

What you are attempting to do seems reasonable... and I think the problem here is that you've got some syntax errors in your join conditions. Instead of what you have, try:

SELECT 
  yt.screen_name,
  tw.screen_name,
  gp.id,
  fb.title,
  dom.domain_name,
  dom.type,
  dom.url
FROM
  domain AS dom
  LEFT JOIN youtube AS yt
    ON yt.company_name = dom.company_name 
  LEFT JOIN twitter AS tw
    ON tw.company_name = dom.company_name 
  LEFT JOIN googleplus AS gp
    ON gp.company_name = dom.company_name 
  LEFT JOIN facebook AS fb
    ON fb.company_name = dom.company_name 
WHERE 
  yt.screen_name IS NOT NULL
  OR tw.screen_name IS NOT NULL
  OR gp.id IS NOT NULL
  OR fb.title IS NOT NULL
ORDER BY dom.url

The difference here is that instead of iterating through several versions of dom1, dom2, dom3, we're using the domain table (aliased as dom) and joining all of the company tables to dom based on the company name.

I need to be explicit and point out that

 LEFT JOIN youtube AS yt
    ON yt.company_name = dom1.company_name 
    AND domain AS dom2

is not valid SQL syntax, and also you reference values for dom (eg dom.domain_name, dom.type,etc) without actually having a table that's aliased as dom. Instead you referenced your table as dom1 and then tried to iterate through it. I'm not 100% sure, but I think you're thinking of these dom1, dom2, dom3, etc as references to rows in your domain table, perhaps. If so, that isn't necessary. You have a table (domain, aliased as dom) that you want to join with your company lookup tables (youtube, twitter, googleplus, facebook). You don't need dom1, dom2, etc.

answered Jan 22, 2015 at 15:21

Evan Volgas

2,9613 gold badges25 silver badges40 bronze badges

1 Comment

Evan Volgas Over a year ago

I must also agree with diolemo above about joining on company name... generally that's not a good idea. Likewise, I'm not sure why you need four tables for four different social networks. Seems like you could merge them into one social networks table with an id for the social network to which rows pertain... I think your data model could be improved a bit, if you wanted to work on it. But in the near term, the problem you are describing is basically syntax.

Collectives™ on Stack Overflow

MySQL multiple left joins on one table

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related