
use tempdb
go

drop table customers
drop table orders

create table customers (
 custid int primary key,
 custname varchar(10)
)

insert customers values(1,'Abe')
insert customers values(2,'Bob')
insert customers values(3,'Cathy')
insert customers values(4,'Dot')
insert customers values(5,'Ernie')
insert customers values(6,'Fred')
go

drop table orders

-- one-to-many relationship between customers-orders
create table orders (
  orderid int primary key,
  custid  int
)

insert orders values(1001, 1)
insert orders values(1002, 2)
insert orders values(1003, 2)
insert orders values(1004, NULL)
insert orders values(1005, 5)
insert orders values(1006, 6)
insert orders values(1007, 6)

SELECT * FROM orders

SELECT * FROM customers c
  INNER JOIN orders o ON c.custid = o.custid

SELECT * FROM customers c
  INNER JOIN orders o ON o.custid = c.custid

SELECT * FROM customers c
  LEFT OUTER JOIN orders o ON c.custid = o.custid

SELECT * FROM customers c
  right OUTER JOIN orders o ON c.custid = o.custid

SELECT * FROM customers c
  full OUTER JOIN orders o ON c.custid = o.custid
go

-- JOINs by table order not equals sign
SELECT * FROM customers c
  LEFT OUTER JOIN orders o ON o.custid = c.custid

-- add third table 
drop table products

create table products (
 productid int primary key,
 productname varchar(10)
)

insert products values(11, 'Tomato')
insert products values(12, 'Milk')
insert products values(13, 'Oatmeal')
insert products values(14, 'Cereal')
insert products values(15, 'Orange')
insert products values(16, 'Candy')
insert products values(17, 'Sprouts')
go

SELECT * FROM products
go

-- add columns to orders, you can only buy one product
-- one-to-one relationship
alter table orders 
 add productid int
go

alter table orders
 add qty int
go

SELECT * FROM orders

update orders set productid = 12, qty = 1 
 where orderid = 1001
update orders set productid = 14, qty = 2 
 where orderid = 1002
update orders set productid = 15, qty = 1 
 where orderid = 1003
update orders set productid = 16, qty = 2 
 where orderid = 1005
update orders set productid = 16, qty = 3 
 where orderid = 1006
update orders set productid = 16, qty = 1 
 where orderid = 1007
go

SELECT * FROM orders
go
 

SELECT c.custid
	, c.custname
    , p.productid
	, p.productname
    , o.orderid
	, o.qty
FROM dbo.Customers AS c
	INNER JOIN dbo.[Orders] AS o 
		ON c.CustID = o.CustID
	INNER JOIN dbo.Products AS p
		ON o.ProductID = p.ProductID
go

UPDATE orders SET productid = 12, qty = 1 
 WHERE orderid = 1004

-- or
UPDATE orders SET productid = null, qty = null 
 WHERE orderid = 1004

-- what happens here?
-- orders without customers appear, if they have products
SELECT c.custid
	, c.custname
    , p.productid
	, p.productname
    , o.orderid
	, o.qty
FROM dbo.Customers AS c
	RIGHT OUTER JOIN dbo.[Orders] AS o 
		ON c.CustID = o.CustID
	INNER JOIN dbo.Products AS p
		ON o.ProductID = p.ProductID
go

-- what happens here? 
-- nothing different customers that didn't have orders won't 
-- have products
SELECT c.custid
	, c.custname
    , p.productid
	, p.productname
    , o.orderid
	, o.qty
FROM dbo.Customers AS c
	FULL OUTER JOIN dbo.[Orders] AS o 
		ON c.CustID = o.CustID
	INNER JOIN dbo.Products AS p
		ON o.ProductID = p.ProductID
go

SELECT c.custid
	, c.custname
    , p.productid
	, p.productname
    , o.orderid
	, o.qty
FROM dbo.Customers AS c
	FULL OUTER JOIN dbo.[Orders] AS o 
		ON c.CustID = o.CustID
	LEFT OUTER JOIN dbo.Products AS p
		ON o.ProductID = p.ProductID

SELECT c.custid
	, c.custname
    , p.productid
	, p.productname
    , o.orderid
    , o.productid
	, o.qty
FROM dbo.Customers AS c
	FULL OUTER JOIN dbo.[Orders] AS o 
		ON c.CustID = o.CustID
	RIGHT OUTER JOIN dbo.Products AS p
		ON o.ProductID = p.ProductID

-- error, order hasn't been introduced yet
SELECT c.custid
	, c.custname
    , p.productid
	, p.productname
    , o.orderid
	, o.qty
FROM dbo.Customers AS c
	JOIN dbo.Products AS p
		ON o.ProductID = p.ProductID
	JOIN dbo.[Orders] AS o 
		ON c.CustID = o.CustID


-- what happens here? 
-- nothing different customers that didn't have orders won't 
-- have products
SELECT c.custid
	, c.custname
    , p.productid
	, p.productname
    , o.orderid
	, o.qty
FROM dbo.Customers AS c
	FULL OUTER JOIN dbo.[Orders] AS o 
		ON c.CustID = o.CustID
	INNER JOIN dbo.Products AS p
		ON o.ProductID = p.ProductID
go


-- but what if I want the customers that ordered nothing to appear?

SELECT c.custid
	, c.custname
    , p.productid
	, p.productname
    , o.orderid
	, o.qty
FROM dbo.Customers AS c
	FULL OUTER JOIN 
    ( 
      dbo.[Orders] AS o 
	  INNER JOIN dbo.Products AS p
		ON o.ProductID = p.ProductID
    )
    ON c.CustID = o.CustID
go


