Menu
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 
924
In SQL we can retrieve data from multiple tables also by using SELECT with multiple tables which actually results in CROSS JOIN of all the tables. The resulting table occurring from CROSS JOIN of two contains all the row combinations of the 2nd table which is a Cartesian product of tables.
In SQL, to fetch data from multiple tables, the join operator is used. The join operator adds or removes rows in the virtual table that is used by SQL server to process data before the other steps of the query consume the data.
From multiple tables To retrieve information from more than one table, you need to join those tables together. This can be done using JOIN methods, or you can use a second SELECT statement inside your main SELECT query—a subquery.
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 
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; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
