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

Answer 1

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

Answer 2

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.