SQL SELECT 从多个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1416003/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL SELECT from multiple tables
提问by tjcombos
How can I get all products from customers1 and customers2 include their customer names?
如何从customers1 和customers2 获取所有产品，包括他们的客户名称？
customer1 table
cid name1
1 john
2 joe
customer2 table
cid name2
p1 sandy
p2 linda
product table
pid cid pname
1 1 phone
2 2 pencil
3 p1 pen
4 p2 paper
Result should be like this
结果应该是这样的
pid cid pname name1 name2
1 1 phone john NULL
2 2 pencil joe NULL
3 p1 pen NULL sandy
4 p2 paper NULL linda
回答by Rowland Shaw
SELECT p.pid, p.cid, p.pname, c1.name1, c2.name2
FROM product p
LEFT JOIN customer1 c1 ON p.cid = c1.cid
LEFT JOIN customer2 c2 ON p.cid = c2.cid
回答by Donnie C
SELECT pid, cid, pname, name1, name2
FROM customer1 c1, product p
WHERE p.cid=c1.cid
UNION SELECT pid, cid, pname, name1, name2
FROM customer2 c2, product p
WHERE p.cid=c2.cid;
回答by chaos
SELECT `product`.*, `customer1`.`name1`, `customer2`.`name2`
FROM `product`
LEFT JOIN `customer1` ON `product`.`cid` = `customer1`.`cid`
LEFT JOIN `customer2` ON `product`.`cid` = `customer2`.`cid`
回答by Philippe Leybaert
select p.pid, p.cid, c1.name,c2.name
from product p
left outer join customer1 c1 on c1.cid=p.cid
left outer join customer2 c2 on c2.cid=p.cid
回答by Ralf de Kleine
SELECT
pid,
cid,
pname,
name1,
null
FROM
product p
INNER JOIN
customer1 c ON p.cid = c.cid
UNION
SELECT
pid,
cid,
pname,
null,
name2
FROM
product p
INNER JOIN
customer2 c ON p.cid = c.cid
回答by LukeH
SELECT p.pid, p.cid, p.pname, c1.name1, c2.name2
FROM product AS p
LEFT JOIN customer1 AS c1
ON p.cid = c1.cid
LEFT JOIN customer2 AS c2
ON p.cid = c2.cid
回答by Andi ILLank
i think i hve some joined like this from 7 Tables
我想我从 7 个表中加入了一些这样的
SELECT a.no_surat ,
a.nm_anggota ,
a.nrp_nip_anggota ,
a.tmpt_lahir ,
a.tgl_lahir ,
a.bln_lahir ,
a.thn_lahir ,
a.alamat ,
a.keperluan ,
a.nm_jabatan ,
b.id_polsek ,b.nm_polsek,
c.id_polres ,c.nm_polres ,
d.id_pangkat , d.nm_pangkat,
e.id_pejabat , e.nm_pejabat ,
f.id_ket , f.nm_ket,
g.id_pejabat,g.nm_pejabat
FROM tbl_skhp AS a
LEFT JOIN tbl_polsek AS b ON a.id_polsek=b.id_polsek
LEFT JOIN tbl_polres AS c ON a.id_polres=c.id_polres
LEFT JOIN tbl_pangkat AS d ON a.id_pangkat=d.id_pangkat
LEFT JOIN tbl_pejabat AS e ON a.id_pejabat=e.id_pejabat
LEFT JOIN tbl_ket AS f ON a.id_ket=f.id_ket
LEFT JOIN tbl_pejabat AS g ON a.id_pejabat=g.id_pejabat
i hope u understand.... i am just sharing worked code for me.... i am use it to fetch data to my readonly form just for priview...
我希望你理解....我只是为我分享工作代码....我用它来获取数据到我的只读表单只是为了 priview...
