我有五张table,但主要是两张。表itemy是我们可以找到所有项的表。还有obchody\ U parovani表,这是我们可以找到与某个商店相关的物品的表。
表项
ID | nazev | cesta | druh | kvalita
1 | nazev | cesta | 1 | 1
2 | nazev | cesta | 3 | 2
3 | nazev | cesta | 1 | 1
4 | nazev | cesta | 5 | 3
表obchody\ U parovani
ID | obchod | item
1 | 1 | 2
2 | 3 | 3
3 | 1 | 1
我需要确认商品是否在店里。如果是,那么我需要商店的id,如果不是,我需要null。
我想从obchody\u parovani得到的obchod的结果是id 1
ID | nazev | cesta | druh_n | kvalita_n | obchod
1 | nazev | cesta | 1 | 1 | NULL
2 | nazev | cesta | 3 | 2 | 1
3 | nazev | cesta | 1 | 1 | NULL
4 | nazev | cesta | 5 | 3 | 1
纳泽夫,塞斯塔,德鲁恩,科瓦利塔并不重要,我需要列目标。若表包含obchod=1的行,则结果中的than将为1,若不是,则将为null
我当前的sql是:
SELECT i.id, i.nazev, i.cesta, d.druh AS druh_n, k.nazev AS kvalita_n, par.obchod FROM itemy AS i LEFT JOIN itemy_druhy AS d ON d.id = i.druh LEFT JOIN itemy_kvalita AS k ON k.id = i.kvalita LEFT JOIN itemy_rozdeleni AS r ON r.id = i.rozdeleni LEFT JOIN obchody_parovani AS par ON par.item = i.id WHERE par.obchod = 1
1条答案
按热度按时间2ledvvac1#
你可以试着用
OUTER JOIN
基于Itemy
table。架构(mysql v5.7)
查询#1
db fiddle视图
我想你可以试着
par.obchod = 1
条件移动到on
从句where
否则你就是在干什么inner join
从obchody_parovani
```SELECT i.id, i.nazev, i.cesta, d.druh AS druh_n, k.nazev AS kvalita_n, par.obchod
FROM itemy AS i
LEFT JOIN itemy_druhy AS d ON d.id = i.druh
LEFT JOIN itemy_kvalita AS k ON k.id = i.kvalita
LEFT JOIN itemy_rozdeleni AS r ON r.id = i.rozdeleni
LEFT JOIN obchody_parovani AS par ON par.item = i.id and par.obchod = 1