-- John Aromando -- SQL Joins --

use ogcPubLev1

select c.custnum, custname, ordnum, sldate
from Customers as c
inner join Sales as s on s.custnum = C.custnum 

--multiple inner joins, way to check for mistakes (customer not assigned correctly in sales, title identifier doesn't exist)
select c.custnum, custname, ordnum, sldate, t.partnum, bktitle
from Customers as c
inner join Sales as s on s.custnum = C.custnum 
inner join Titles as t on s.partnum = t.partnum
order by custname asc

-- if more than the inner join between the same two, bad data, left side
select c.custnum, custname, ordnum, sldate
from Customers as c
left outer join Sales as s on s.custnum = C.custnum
where ordnum is NULL

-- if more than the inner join between the same two, bad data, right side
select c.custnum, custname, ordnum, sldate
from Customers as c
right outer join Sales as s on s.custnum = C.custnum

select c.custnum, custname, ordnum
from Customers as c
full outer join Sales as s on s.custnum = C.custnum

--self join
select c1.custnum, c1.custname, c1.referredby, c2.custname, c2.custnum
from Customers as c1
inner join Customers as c2 on c1.referredby = c2.custnum
order by c2.custname

--cross join(cartesian join)
select c.custnum, custname, ordnum, sldate
from Customers as c
cross join Sales as s 

-- no duplicates (union all = duplicates)
select* from Titles
union
select* from Obsolete_Titles

select*, 'Current' as 'Table' from Titles
union
select*, 'Obsolete' as 'Table' from Obsolete_Titles
order by 'Table' desc

--intersect (common to both tables)
select bktitle from titles
intersect
select bktitle from obsolete_titles

--except (all from 1st that are not in 2nd)
select * from obsolete_titles
except
select * from titles

select *
from Customers as c, sales as s, titles as t
where c.custnum = s.custnum and s.partnum = t.partnum

-- all sales people whether or not they sold something
select Slspers.repid, lname, ordnum as OrdNum 
from Slspers
left join Sales on Slspers.repid = Sales.repid
where ordnum is NULL