SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [Customers](
  [CustomerID] INTEGER PRIMARY KEY AUTOINCREMENT, 
  [Name] TEXT, 
  [Product] TEXT);
✓
CREATE TABLE [Products](
  [ProductID] INTEGER PRIMARY KEY AUTOINCREMENT, 
  [Category] TEXT UNIQUE, 
  [ProductName] TEXT UNIQUE, 
  [Count] INT);
✓
INSERT INTO Products (Category, ProductName, Count)
VALUES ('TV', 'SONY BRAVIA A8H', 29),
('MotherBoard', 'ASUS Prime h510m-k', 18),
('Monitor', 'ASUS va27ehe', 21),
('LTE Modem', 'UTEL L443', 9);
✓
INSERT INTO Customers (Name, Product)
VALUES ('Alex', 'ASUS va27ehe'),
('Alex', 'UTEL L443'),
('Amanda', 'ASUS va27ehe'),
('Amanda', 'GIGABYTE'),
('Amanda', 'SONY BRAVIA A8H');
✓
SELECT * FROM Products;


ProductID
Category
ProductName
Count


1
TV
SONY BRAVIA A8H
29

2
MotherBoard
ASUS Prime h510m-k
18

3
Monitor
ASUS va27ehe
21

4
LTE Modem
UTEL L443
9


SELECT * FROM Customers;


CustomerID
Name
Product


1
Alex
ASUS va27ehe

2
Alex
UTEL L443

3
Amanda
ASUS va27ehe

4
Amanda
GIGABYTE

5
Amanda
SONY BRAVIA A8H


SELECT c.CustomerID
  , c.Name
  , c.Product
  , p.ProductID
FROM Customers c
LEFT OUTER JOIN Products p
ON c.Product = p.ProductName;


CustomerID
Name
Product
ProductID


1
Alex
ASUS va27ehe
3

2
Alex
UTEL L443
4

3
Amanda
ASUS va27ehe
3

4
Amanda
GIGABYTE
null

5
Amanda
SONY BRAVIA A8H
1


SELECT c.CustomerID
  , c.Name
  , c.Product
  , p.ProductID
FROM Customers c
INNER JOIN Products p
ON c.Product = p.ProductName;


CustomerID
Name
Product
ProductID


1
Alex
ASUS va27ehe
3

2
Alex
UTEL L443
4

3
Amanda
ASUS va27ehe
3

5
Amanda
SONY BRAVIA A8H
1

ProductID	Category	ProductName	Count
1	TV	SONY BRAVIA A8H	29
2	MotherBoard	ASUS Prime h510m-k	18
3	Monitor	ASUS va27ehe	21
4	LTE Modem	UTEL L443	9

CustomerID	Name	Product
1	Alex	ASUS va27ehe
2	Alex	UTEL L443
3	Amanda	ASUS va27ehe
4	Amanda	GIGABYTE
5	Amanda	SONY BRAVIA A8H