I’m trying to do some DB during the lockdown and would appreciate some help, since I have been doing this for 5 hours without luck. Got a PDF from a book and sadly, the solutions part isn’t readable.
Here are two tables:
x
**Table 1 named Laptop**
Laptop ID(PK) Name
1 Lenovo
2 Asus
3 Mac
**Table 2 named Buyers**
Buyer ID(PK) Buyer Name Laptop ID(FK) Country
A Jet Li 1 China
B Hermione 1 UK
C Mercato 2 Spain
The activity requests that I create an “SQL statement that selects all bags names and their buyer name (so 2 columns only), and to show null values as well” because Mac doesn’t have a buyer.
I have been trying JOIN and UNION, but no lock.
SELECT Name from Laptop
UNION
SELECT Buyer name from Buyers;
Thank you very much
Advertisement
Answer
You need an OUTER JOIN
:
SELECT
l.name,
b.buyer_name
FROM
laptop l
LEFT OUTER JOIN buyers b
ON l.laptop_id = b.bag_id;
Demo:
with laptop as(
select 1 laptop_id, 'Lenevo' name from dual union all
select 2 laptop_id, 'Asus' name from dual union all
select 3 laptop_id, 'Mac' name from dual
),
buyers as(
select 'Jet Li' buyer_name, 1 bag_id from dual union all
select 'Hermione' buyer_name, 1 bag_id from dual union all
select 'Mercato' buyer_name, 2 bag_id from dual
)
SELECT
l.name,
b.buyer_name
FROM
laptop l
LEFT OUTER JOIN buyers b
ON l.laptop_id = b.bag_id;
NAME BUYER_NAME
---------- --------------------
Lenevo Jet Li
Lenevo Hermione
Asus Mercato
Mac NULL