SQL SELECT from multiple tables

Question

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

Rowland Shaw · Accepted Answer · 2013-07-24 11:29:34Z

111

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

edited Jul 24, 2013 at 11:29

answered Sep 12, 2009 at 20:15

Rowland Shaw

38.2k15 gold badges104 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Keibosh Over a year ago

There is declaration that p is the product table from the second line. "FROM product p"

Myxtic · Accepted Answer · 2013-04-09 16:00:04Z

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;

edited Apr 9, 2013 at 16:00

Myxtic

5,7095 gold badges51 silver badges69 bronze badges

answered Sep 12, 2009 at 20:14

Donnie C

6491 gold badge6 silver badges9 bronze badges

3 Comments

Rowland Shaw Over a year ago

name2 is an unknown column in the first half of that union (and name1 in the second half)

OMG Ponies Over a year ago

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.

Rowland Shaw Over a year ago

@rexem there is in the customer2 table

chaos · Accepted Answer · 2009-09-12 20:14:44Z

SELECT `product`.*, `customer1`.`name1`, `customer2`.`name2` FROM `product` LEFT JOIN `customer1` ON `product`.`cid` = `customer1`.`cid` LEFT JOIN `customer2` ON `product`.`cid` = `customer2`.`cid`

Philippe Leybaert · Accepted Answer · 2009-09-12 20:15:09Z

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

Ralf de Kleine · Accepted Answer · 2018-08-23 10:39:27Z

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

LukeH · Accepted Answer · 2009-09-12 20:15:54Z

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

Andi ILLank · Accepted Answer · 2019-07-29 13:37:36Z

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

The question was already answered and this answer does not refer to the question.
This answer confuses the followers/ answer seekers. it would be great if you can answer to the exact question

Collectives™ on Stack Overflow

SQL SELECT from multiple tables

7 Answers 7

1 Comment

3 Comments

Comments

Comments

Comments

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

3 Comments

Comments

Comments

Comments

Comments

2 Comments

Linked

Related