All for Joomla All for Webmasters

SQL SELECT from Multiple Tables

This statement is used to retrieve fields from multiple tables. To do so, we need to use join query to get data from multiple tables.

Let’s see the example for the select from multiple tables:

  1. SELECT orders.order_id, suppliers.name
  2. FROM suppliers
  3. INNER JOIN orders
  4. ON suppliers.supplier_id = orders.supplier_id
  5. ORDER BY order_id;

Let us take three tables, two tables of customers named customer1 and customer2 and the third table is product table.

Customer1 table

Cus_id Name1
1 Jack
2 Jill

Customer2 table

Cus_id Name2
1 Sandy
2 Venus

Product table

P_id Cus_id P_name
1 1 Laptop
2 2 Phone
3 P1 Pen
4 P2 Notebook

Example syntax to select from multiple tables:

  1. SELECT p. p_id, p.cus_id, p.p_name, c1.name1, c2.name2
  2. FROM product AS p
  3. LEFT JOIN customer1 AS c1
  4. ON p.cus_id=c1.cus_id
  5. LEFT JOIN customer2 AS c2
  6. ON p.cus_id = c2.cus_id
P_id Cus_id P_name P_name P_name
1 1 Laptop Jack NULL
2 2 Phone Jill NULL
3 P1 Pen NULL Sandy
4 P2 Notebook NULL Venus
PinIt
submit to reddit

Leave a Reply

Top