SQL查询从相同ID的不同表中获取所有数据
问题描述:
对不起,如果这太元素,但我不能解决它。不知道如何寻找它的信息之一:SQL查询从相同ID的不同表中获取所有数据
我有三个表:
提供商
id_provider name
---------- -----------
100 John
101 Sam
102 Peter
联系
id_contact RowNo Email
---------- ----------- ----------------
100 1 [email protected]
100 2 [email protected]
101 1 [email protected]
101 2 [email protected]
产品
Id_product RowNo Product
---------- ----------- ------------------------
100 1 John’s 1st product
100 2 John’s 2nd product
101 1 Sam’s 1st product
101 2 Sam’s 2nd product
101 3 Sam’s 3rd product
我需要aq uery显示从这样三个表中的所有数据:
Id name id_contact RowNo Email Id_Product RowNo Product
100 John 100 1 [email protected] 100 1 John’s 1st product
100 John 100 2 [email protected] 100 2 John’s 2st product
101 Sam 101 1 [email protected] 101 1 Sam's 1st product
101 Sam 101 2 [email protected] 101 2 Sam's 2nd product
101 Sam null null null 101 3 Sam's 3rd product
102 Peter null null null null null null
我想所有的连接,但我知道我不能使它发挥作用。
非常感谢
答
您可以使用下面的查询:
SELECT t1.id_provider AS Id, t1.name,
t2.id_contact, t2.cRowNo, t2.Email,
t2.Id_product, t2.Product
FROM Provider AS t1
LEFT JOIN (
SELECT COALESCE(id_contact, id_product) AS id,
c.id_contact, c.RowNo AS cRowNo, c.Email,
p.Id_product, p.Product, p.RowNo AS pRowNo
FROM Contact AS c
FULL JOIN Product AS p ON p.id_product = c.id_contact AND p.RowNo = c.RowNo
) AS t2 ON t1.id_provider = t2.id
查询在Contact
和Product
表之间执行FULL JOIN
,并将从FULL JOIN
到Provider
表导出的表加入。因为我们无法事先知道这两个表,Contact
或Product
,包含每个id
最行
一个FULL JOIN
是必需的。
答
select *
from Provider P1
left join Contact C2
on C2.id_contact = P1.id_provider
left join Product P2
on P2.id_product = P1.id_provider
答
SELECT prov.*,
c.*,
prod.*
FROM PROVIDER prov
LEFT JOIN Product prod ON prod.id_product = prov.id_provider
LEFT JOIN Contact c ON prov.id_provider = c.id_contact
AND prod.RowNo = c.RowNo
使用左联接,但加盟商对产品第一,然后联系