3 Table Join in MySQL

Question

Hello!

Say I have 3 tables. Table1 contains client_id and client_name. Table2 contains client_id, client_catid_1, and client_catid_2. These to tables are joined based on the, client_id. Now the third table, contains category_id and category_name. How can join the third table based on the category id's and get the category_name? example:

Table 1:

client_id | client_name
-----------------------
121231231 | Some name
345234666 | Another Name
-----------------------

Table 2:

client_id | client_catid_1 | client_catid_2
-------------------------------------------
121231231 | 22             | 79
345234666 | 34             | 566
------------------------------------------

Table 3:

category_id | category_name
----------------------------
22          | category 22
34          | category 34
79          | category 79
566         | category 566
----------------------------

Then the output:

client_id | client_name | client_cat1 | client_cat2
---------------------------------------------------
121231231 | Some name   | category 22 | category 79
345234666 | Another     | category 34 | category 566
---------------------------------------------------

And the current query:

SELECT client.*,
cat1id.client_catid_1 as cat1,
cat2id.client_catid_2 as cat2
FROM tb_clients AS client
LEFT JOIN tb_clients_categories cat1id ON client.client_id = cat1id.client_id
LEFT JOIN tb_clients_categories cat2id ON client.client_id = cat2id.client_id
WHERE client.client_id = 65447

Also, I am not asking about database normalization or design. I only say this because people tend to miss your question and start trying to inform you of better database design. I understand this. I am working with something I did not originally put together and changing the DB is not an option!

Thanks in advance!

Answer 1

Use:

   SELECT a.client_id,
          a.client_name,
          c1.category_name AS client_cat1,
          c2.category_name AS client_cat2
     FROM tb_clients a
LEFT JOIN tb_clients_categories b ON b.client_id = a.client_id
LEFT JOIN TABLE C c1 ON c1.category_id = b.client_catid_1
LEFT JOIN TABLE C c2 ON c2.category_id = b.client_catid_2
    WHERE c1.category_name = 'Something' 
       -- because you can't refer to a column (client_cat1) alias in the WHERE

That will give you a list of clients, and optionally their category names. If a relationship does not exist between tb_clients_categories and tb_clients, the client name will still appear and the client_cat1 and client_cat2 will be null. If you don't want this, remove the LEFT keyword on the JOINs for only clients with two categories associated to appear in the output.

Answer 2

SELECT client.*,
cat1id.client_catid_1 as cat1,
cat1id.client_catid_2 as cat2,
cat1.category_name,
cat2.category_name
FROM tb_clients AS client
LEFT JOIN tb_clients_categories cat1id ON client.client_id = cat1id.client_id
LEFT JOIN category cat1 ON category.cat_id = cat1id.client_catid_1
LEFT JOIN category cat2 ON category.cat_id = cat2id.client_catid_2
WHERE client.client_id = 65447

Answer 3

A LEFT JOIN statement can contain any filter. Use OR in the join clause and you do not need to repeat the first table.:

SELECT client.*,
  cats.category_id AS client_catid
FROM tb_clients AS client
  LEFT JOIN tb_clients_categories cat1id ON client.client_id = cat1id.client_id
  LEFT JOIN tb_categories cats ON 
    (cats.category_id = cat1id.client_cat1 OR 
     cats.category_id = cat1id.client_cat2)
WHERE client.client_id = 65447