Select a value from Table 2 that depends on another value from Table 1 (Oracle SQL)

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:

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

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