SQL Basics: Simple JOIN

For this challenge you need to create a simple SELECT statement that will return all columns from the products table, and join to the companies table so that you can return the company name.

products table schema

• id
• name
• isbn
• company_id
• price

companies table schema

• id
• name

You should return all product fields as well as the company name as "company_name".

NOTE: Your solution should use pure SQL. Ruby is used within the test cases to do the actual testing.

 

 

Solution:

SELECT 
  PD.id, 
  PD.name, 
  PD.isbn, 
  PD.company_id, 
  PD.price, 
  CP.name AS company_name 
FROM products AS PD
LEFT JOIN companies AS CP 
ON PD.company_id = CP.id

 

If solved differently,

 

SELECT p.*,
       c.name AS company_name
  FROM products p,
       companies c
 WHERE c.id = p.company_id

 

 

Result:

id	name	company_id	isbn	price	company_name
1	Fantastic Paper Computer	1	016642543-5	43.78	DuBuque, Rodriguez and Wehner
2	Practical Steel Keyboard	2	470494922-2	73.35	Breitenberg, Veum and Zieme
3	Gorgeous Paper Knife	1	778924263-6	50.77	DuBuque, Rodriguez and Wehner
4	Mediocre Concrete Lamp	1	867612741-7	52.47	DuBuque, Rodriguez and Wehner
5	Mediocre Copper Plate	1	090592032-5	16.36	DuBuque, Rodriguez and Wehner
6	Lightweight Plastic Bottle	1	548505837-X	0.71	DuBuque, Rodriguez and Wehner
7	Lightweight Plastic Bench	2	512415609-0	97.2	Breitenberg, Veum and Zieme
8	Gorgeous Iron Clock	1	381296142-3	14.49	DuBuque, Rodriguez and Wehner
9	Durable Bronze Clock	1	382193280-5	54.74	DuBuque, Rodriguez and Wehner
10	Practical Copper Chair	1	245162326-8	62.87	DuBuque, Rodriguez and Wehner

 

 

Reference

PostgreSQL OUTER 조인