时间戳

首页 > 解决方案 > Match 2 columns from the same table to the same single column in another table (total of 3 joins)

php - Match 2 columns from the same table to the same single column in another table (total of 3 joins)

问题描述

I have 3 tables.

sheets TABLE
sheet_id
brand_id
sheet_color
sheet_code
adhesive_id_1
adhesive_id_2
adhesive_id_3
factor_1
factor_2
factor_3

sheet_brands TABLE
brand_id
brand_name
surface_type_id

adhesives TABLE
adhesive_id
match_code
match_name

I need to match:

1) sheets.brand_id to sheet_brands.brand_id to get the brand_name

2) sheets.adhesive_id_1 to adhesives.adhesive_id to get the match_code and match_name

3) sheets.adhesive_id_2 to adhesives.adhesive_id to get the match_code and match_name

I have spent awhile searching and have not found a solution that works. I believe this type of JOIN is unique because I need to match 2 columns from the same table to the same single column in another table.

This is the closest I came to solving the problem BUT it only matches the first adhesive_id_1. It does not match adhesive_id_2 or adhesive_id_3.

$sql = "SELECT sheet_color, sheet_code, factor_1, factor_2, brand_name, match_code, match_name FROM sheets LEFT JOIN sheet_brands ON sheet_brands.brand_id = sheets.brand_id LEFT JOIN adhesives ON adhesives.adhesive_id = sheets.adhesive_id_1";

标签： phpmysqlsqljoinleft-join

解决方案

您必须加入表adhesives两次才能同时获得match_codes 和match_names：

SELECT s.sheet_color, s.sheet_code, s.factor_1, s.factor_2, 
       b.brand_name, 
       a1.match_code match_code1, a1.match_name match_name1,
       a2.match_code match_code2, a2.match_name match_name2
FROM sheets s
LEFT JOIN sheet_brands b ON b.brand_id = s.brand_id 
LEFT JOIN adhesives a1 ON a1.adhesive_id = s.adhesive_id_1
LEFT JOIN adhesives a2 ON a2.adhesive_id = s.adhesive_id_2

为表使用别名并使用这些别名限定列名。
如果您还想要match_codeand match_nameforadhesive_id_3您将需要再加入：

SELECT s.sheet_color, s.sheet_code, s.factor_1, s.factor_2, 
       b.brand_name, 
       a1.match_code match_code1, a1.match_name match_name1,
       a2.match_code match_code2, a2.match_name match_name2,
       a3.match_code match_code3, a3.match_name match_name3
FROM sheets s
LEFT JOIN sheet_brands b ON b.brand_id = s.brand_id 
LEFT JOIN adhesives a1 ON a1.adhesive_id = s.adhesive_id_1
LEFT JOIN adhesives a2 ON a2.adhesive_id = s.adhesive_id_2
LEFT JOIN adhesives a3 ON a3.adhesive_id = s.adhesive_id_3

推荐阅读