我有两个表,选项和产品。我想从选项中选择所有选项,但前提是 options.id 存在于任何产品列中。这是我的两个表:
选项表:
id option value
1 'kategorija' 'Muški'
2 'kategorija' 'Ženski'
3 'kategorija' 'Dječji'
4 'brand' 'Casio'
5 'brand' 'Lorus'
6 'brand' 'Seiko'
7 'brand' 'Citizen'
8 'mehanizam' 'Quartz'
9 'mehanizam' 'Automatik'
10 'mehanizam' 'Eco-Drive'
11 'brojcanik' 'Analogni'
12 'brojcanik' 'Digitalni'
13 'grupa' 'Satovi'
14 'grupa' 'Naocale'
和第二个表产品:
id grupa brand mehanizam brojcanik kategorija
10380 '13' '4' '8' '11' '2'
10560 '13' '4' '9' '12' '1'
11100 '13' '6' '8' '11' '2'
12380 '14' '7' '8' '11' '2'
12490 '13' '6' '9' '11' '1'
15720 '14' '6' '9' '12' '1'
16550 '14' '5' '8' '12' '3'
我的查询尝试:
SELECT * FROM options WHERE EXISTS( SELECT 1 FROM products WHERE grupa="14" AND brand=options.id OR mehanizam=options.id OR brojcanik=options.id OR kategorija=options.id)
结果应该是:
Array
(
[0] => Array
(
[id] => 14
[option] => grupa
[value] => Naocale
)
[1] => Array
(
[id] => 7
[option] => brand
[value] => Citizen
)
[2] => Array
(
[id] => 8
[option] => mehanizam
[value] => Quartz
)
[3] => Array
(
[id] => 11
[option] => brojcanik
[value] => Analogni
)
[4] => Array
(
[id] => 2
[option] => kategorija
[value] => Zenski
)
)
对于产品 ID 12380 的一行,这是 grupa 14 的数组。对于在产品行中找到 grupa 14 的其余产品,数组应该继续。我不知道这是否仅适用于 mysql 查询。如果不可能,我将不得不与 php 进行比较,这是我试图避免的。
谢谢
根据您的说法,
I want to select all from options but only if options.id exists in any of the products columns.
您可以在子查询中使用 UNION ALL
组合所有值,并将结果与表 options
连接。
SELECT DISTINCT a.*
FROM options a
INNER JOIN
(
SELECT brand col FROM products WHERE grupa = 14
UNION ALL
SELECT mehanizam col FROM products WHERE grupa = 14
UNION ALL
SELECT brojcanik col FROM products WHERE grupa = 14
UNION ALL
SELECT kategorija col FROM products WHERE grupa = 14
) b ON a.id = b.col
更好的方法,
SELECT *
FROM options a
WHERE EXISTS
(
SELECT 1
FROM products b
WHERE b.grupa = '14' AND
a.ID IN (brand, mehanizam, brojcanik, kategorija)
)
我是一名优秀的程序员,十分优秀!