mysql - SQL 表连接，查询问题

Question

我将此查询用于连接表，它有效但只返回 1 个值，我想要颜色明智的数据而不是所有数据。这是我的查询及其 fiddle

  SELECT *,
         (SELECT pname
            FROM tbl_product
           WHERE id = tbl_productcolor.pid
          ) as productname,
         (SELECT image
            FROM tbl_product
           WHERE id = tbl_productcolor.pid
          ) as image
    FROM tbl_productcolor
   WHERE id = 1
ORDER BY id ASC;

这是我的两张 table

tbl_product

id      category        pcode       pname       image
-----------------------------------------------------
1       cat1            567         product1    img1.jpg
2       cat2            544         product2    img2.jpg
3       cat3            5665        product3    img3.jpg
4       cat1            5665        t1          img4.jpg
5       cat1            567         t5          img5.jpg
6       cat3            75          t8          img6.jpg
7       cat4            12          name        img7.jpg

tbl_productcolor

    id          pid         color
-----------------------------
1           1           Black (79)
2           1           Grey (205)
3           1           Green (69)
4           1           Blue (89)
5           1           Purple (14)
6           1           Yellow (31)
7           1           Brown (124)
8           1           Beige (95)
9           1           Cream (164)
10          1           White (09)
11          2           Multi-coloured (113)
12          2           Black (79)
13          3           Pattern (84)
14          3           Metallic (48)
15          3           Black (79)
16          3           Orange (29)
17          3           Beige (95)
18          4           Green (69)
19          4           Red (33)
20          4           Yellow (31)
21          4           Brown (124)
22          4           Beige (95)
23          4           White (09)
24          5           Black (79)
25          5           Green (69)
26          5           Purple (14)
27          5           Red (33)
28          6           Metallic (48)
29          6           Black (79)
30          6           Grey (205)
31          6           Green (69)
32          6           Purple (14)
33          6           Pink (20)
34          6           Red (33)
35          7           Pattern (84)
36          7           Multi-coloured (113)
37          7           Metallic (48)
38          7           Black (79)
39          7           Grey (205)
40          7           Green (69)
41          7           Blue (89)
42          7           Purple (14)
43          7           Pink (20)
44          7           Red (33)
45          7           Orange (29)
46          7           Yellow (31)
47          7           Brown (124)
48          7           Beige (95)
49          7           Cream (164)
50          7           White (09)

Answer 1

如果我做对了，您需要规范化 tbl_productcolor 表，让您的生活更轻松一些。

你应该把你的 table 一分为二:

tbl_productcolor

id          pid      color_id
-----------------------------
1           1        1   
29          6        1
2           1        2
30          6        2

和

tbl_colors

id   name
----------------------
1    Black (79)
2    Grey (205)

对于您当前的结构，我猜您需要一个查询:

select 
C1.ID,
C1.PID,
c1.color,
p.pname,
p.image 
FROM tbl_productcolor C1 
JOIN tbl_productcolor C2 ON C1.Color=C2.Color AND C2.ID=1
JOIN tbl_product p ON C1.PID=p.ID

order by p.id asc ;

SQLFiddle demo