MySQL join through relational table

Question

Hi I can't seem to find the right way to write this query. I have two entities websites and clients, and a table that relates them through their id fields.

This is a many to many relationship. i.e. a website can have multiple clients and a client can have multiple websites.

I am trying to write a query that returns all the websites with the clients that belong to them. I want to return all the websites even if they have no clients associated with them. Here is the query that I am working with at the moment:

the three tables are ost_sites = websites, ost_site_auth = relational table, ost_clients = clients

SELECT 
    ost_sites.site_id, 
    ost_sites.name,
    ost_sites.site_url,
    ost_site_auth.site_id,
    ost_site_auth.client_id 
    ost_clients.client_id,
    CONCAT_WS(" ", ost_clients.lastname, ost_clients.firstname) as name,
FROM ost_sites
LEFT JOIN (ost_site_auth, ost_clients) 
    ON (ost_sites.site_id=ost_site_auth.site_id 
        AND ost_site_auth.client_id=ost_clients.client_id)
GROUP BY ost_sites.name

I get a result set but it doesn't return all the sites, and all of the rows don't have clients associated with them.

Thanks so much for any help!

Edit:

Here are the columns for the tables:

ost_site

site_id |    name    | site_url
1          facebook    facebook.com
2          twitter     twitter.com
3          tubmblr     tumblr.com
4          google      google.com

ost_site_auth

(notice no site_id = 3 in auth list)

id |   site_id    | client_id
1        1             1
2        1             2
3        2             1 
4        2             2
5        4             1
6        4             4

ost_client

client_id  |  firstname  |  lastname
1              wilma         flintstone
2              bam           bam
3              fred          flintstone
4              barney        rubble

expected output:

site_id |    name    |    site_url    |      client_name     |
1          facebook    facebook.com        wilma flintstone
1          facebook    facebook.com        bam bam
2          twitter     twitter.com         wilma flintstone
2          twitter     twitter.com         bam bam
4          google      google.com          wilma flintstone
4          google      google.com          barney rubble
3          tumblr      tumlr.com           NULL

What result _do_ you get from this query? Could you post an example output? Or a part of your data? — geomagas, Oct 22 '13 at 16:08
I'll edit the post with general data, but I can't put in specific data as names and websites pertain to my job. — Jake B, Oct 22 '13 at 16:37
okay... four queries here: http://sqlfiddle.com/#!2/58974/6/0 — gloomy.penguin, Oct 22 '13 at 16:54
and I even added 5th query at the top of this link.... http://sqlfiddle.com/#!2/58974/7/0 --> From your example... you just want to use a `left outer join`. I'm not sure how the first query I initially suggested doesn't fit what you want...? — gloomy.penguin, Oct 22 '13 at 16:58
In the absence of any aggregating functions, the use of a `GROUP BY` clause is inappropriate. Perhaps you meant to include the `DISTINCT` operator. — Strawberry, Oct 22 '13 at 17:05

gloomy.penguin · Accepted Answer · 2013-10-22T17:04:10.773

Your join looks a bit off... try this

SELECT 
    ost_sites.site_id, 
    ost_sites.name,
    ost_sites.site_url,
    ost_site_auth.site_id,
    ost_site_auth.client_id 
    ost_clients.client_id,
    CONCAT_WS(" ", ost_clients.lastname, ost_clients.firstname) as name

FROM ost_sites

LEFT OUTER JOIN ost_site_auth 
    ON ost_sites.site_id=ost_site_auth.site_id 

LEFT OUTER JOIN ost_clients
    ON ost_site_auth.client_id=ost_clients.client_id

ORDER BY ost_sites.name

Let me try to explain this a little for you...

We start with the ost_sites table and we want all the results from that regardless of if anything matches in the other tables.
Then, we do a left outer join to the table ost_site_auth. That means that if something from ost_site_auth does not match something in ost_sites, it will not be returned. However, something in ost_sites that doesn't match something in ost_site_auth will be returned because of the left outer part.
Next, we repeat the left outer join for the ost_clients.

Not sure what you want... Let's pretend we have this data represented in the tables:

Site #1 has no clients
Site #2 has one client: A
Site #3 has two clients: B, C
Site #4 has three clients: D, E, F
Site #5 has no clients
Clients G and H have no associated site

Query One

      SELECT 
          ost_sites.site_id as SITE,   
          ost_clients.client_id as CLIENT

      FROM ost_sites

      LEFT OUTER JOIN ost_site_auth 
          ON ost_sites.site_id=ost_site_auth.site_id 

      LEFT OUTER JOIN ost_clients
          ON ost_site_auth.client_id=ost_clients.client_id

      ORDER BY ost_sites.site_id, ost_clients.client_id

That would return (basically)

SITE     CLIENT 
1        NULL
2        A
3        B
3        C
4        D
4        E
4        F
5        NULL

Query Two

      SELECT 
          ost_sites.site_id as SITE,   
          ost_clients.client_id as CLIENT

      FROM ost_sites

      JOIN ost_site_auth 
          ON ost_sites.site_id=ost_site_auth.site_id 

      JOIN ost_clients
          ON ost_site_auth.client_id=ost_clients.client_id

      ORDER BY ost_sites.site_id, ost_clients.client_id

That would return (basically)

SITE     CLIENT  
2        A
3        B
3        C
4        D
4        E
4        F

Query three

      SELECT 
          ost_sites.site_id as SITE,   
          ost_clients.client_id as CLIENT

      FROM ost_sites

      FULL OUTER JOIN ost_site_auth 
          ON ost_sites.site_id=ost_site_auth.site_id 

      FULL OUTER JOIN ost_clients
          ON ost_site_auth.client_id=ost_clients.client_id

      ORDER BY ost_sites.site_id, ost_clients.client_id

That would return (basically)

SITE     CLIENT  
1        NULL
2        A
3        B
3        C
4        D
4        E
4        F
5        NULL
NULL     G
NULL     H

Query four

      SELECT DISTINCT ost_sites.site_id as SITE 

      FROM ost_sites

      LEFT OUTER JOIN ost_site_auth 
          ON ost_sites.site_id=ost_site_auth.site_id 

      LEFT OUTER JOIN ost_clients
          ON ost_site_auth.client_id=ost_clients.client_id

      ORDER BY ost_sites.site_id 
      ORDER BY ost_sites.site_id

That would return (basically)

SITE         
2
3
4

Query five

      SELECT 
          ost_sites.site_id as SITE,
          count(ost_clients.client_id) as CLIENT_COUNT

      FROM ost_sites

      JOIN ost_site_auth 
          ON ost_sites.site_id=ost_site_auth.site_id 

      JOIN ost_clients
          ON ost_site_auth.client_id=ost_clients.client_id

      GROUP BY ost_sites.site_id 
      ORDER BY ost_sites.site_id

That would return (basically)

SITE        CLIENT_COUNT  
2           1
3           2
4           3

Query five

      SELECT 
          ost_sites.site_id as SITE,
          count(ost_clients.client_id) as CLIENT_COUNT

      FROM ost_sites

      LEFT OUTER JOIN ost_site_auth 
          ON ost_sites.site_id=ost_site_auth.site_id 

      LEFT OUTER JOIN ost_clients
          ON ost_site_auth.client_id=ost_clients.client_id

      GROUP BY ost_sites.site_id 
      ORDER BY ost_sites.site_id

That would return (basically)

SITE        CLIENT_COUNT  
1           0
2           1
3           2
4           3
5           0

on the second `join` I had accidentally left `and` there instead of changing it to `on`... that has been updated/edited now. — gloomy.penguin, Oct 22 '13 at 16:08
This works and gives me all of the websites and one client associated with that website (NULL if that website has no clients). I just need it to return multple rows if that website has multiple clients. Maybe I could have query just return all websites with clients and then perform another query to find which ones don't have clients? — Jake B, Oct 22 '13 at 16:14
is that what you want? if you do a straight `join` it will only return things that have matching rows in all three tables. if you would do a `right outer join` it would go in reverse joining order, so you would get all clients regardless of if they had rows in `ost_site_auth` and then `ost_sites`. (It's always better to use a `left outer join` because it is more common and therefore easier to visualize/understand.) — gloomy.penguin, Oct 22 '13 at 16:15
If you want to return everything where it matches and even if it doesn't, you should use the `full outer join` that the other answer is referring to... Where the data matches up, you would get a full row. But you would also get sites without clients and clients. without sites. — gloomy.penguin, Oct 22 '13 at 16:19
So you don't want to see sites that do not have any clients? — gloomy.penguin, Oct 22 '13 at 16:23
This would most likely be the best way to describe the result set I am thinking of. For a site return all of the clients associated with it only if it has at least one client. Then union all of the sites that have no clients. Does that make sense? — Jake B, Oct 22 '13 at 16:27
This would give something like site1 => client 1, site1 => client2, site2 => client3, site3 => NULL, site4 => client3, site4 => client2 — Jake B, Oct 22 '13 at 16:29
Wow that works thanks so much I wish I could upvote your answer more. This whole post is actually really helpful. I tried to do a FULL OUTTER JOIN like you suggested earlier but mysql kept saying there was a syntax error on FULL. — Jake B, Oct 22 '13 at 17:03
I was essentially just changing 3 words per query lol... maybe you understand joins a little bit better now? — gloomy.penguin, Oct 22 '13 at 17:06
Yes I'm sure I'm not the only one that this is helpful to. Thanks agian — Jake B, Oct 22 '13 at 17:07

score 0 · Answer 2 · edited May 23 '17 at 12:30

0

Check out

Full Outer Join in MySQL

I think all you really need to do is do the same query as a right outer join as well and union them.

edited May 23 '17 at 12:30

Community

1
1

answered Oct 22 '13 at 16:10

John Wesley Gordon

910
2
17
39

MySQL join through relational table

Edit:

2 Answers2