当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - 3 Table Join in MySQL

问题描述:

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!

网友答案:

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.

网友答案:
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
网友答案:

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
分享给朋友:
您可能感兴趣的文章:
随机阅读: