74

How can I get all products from customers1 and customers2 include their customer names?

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
chaos
  • 122,029
  • 33
  • 303
  • 309
tjcombos
  • 753
  • 2
  • 6
  • 8

7 Answers7

109
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
Rowland Shaw
  • 37,700
  • 14
  • 97
  • 166
23
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;
Myxtic
  • 5,679
  • 5
  • 48
  • 69
Donnie C
  • 659
  • 1
  • 6
  • 9
  • name2 is an unknown column in the first half of that union (and name1 in the second half) – Rowland Shaw Sep 12 '09 at 20:20
  • There's no name2 column in either CUSTOMER table - you need to rearrange the customer name columns, swapping for null to match the desired output. Once that's done, you do provide an alternative to the LEFT JOINs most of us came up with. – OMG Ponies Sep 12 '09 at 20:20
  • @rexem there is in the customer2 table – Rowland Shaw Sep 12 '09 at 20:21
5
SELECT `product`.*, `customer1`.`name1`, `customer2`.`name2`
FROM `product`
LEFT JOIN `customer1` ON `product`.`cid` = `customer1`.`cid`
LEFT JOIN `customer2` ON `product`.`cid` = `customer2`.`cid`
chaos
  • 122,029
  • 33
  • 303
  • 309
5
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
Philippe Leybaert
  • 168,566
  • 31
  • 210
  • 223
3
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
Ralf de Kleine
  • 11,464
  • 5
  • 45
  • 87
1
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
LukeH
  • 263,068
  • 57
  • 365
  • 409
-5

i think i hve some joined like this from 7 Tables

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...

Andi ILLank
  • 3
  • 1