subtraction from multiple tables - sql

Question

Im doing a few exersices for the my head around SQL queries. Im trying to achieve all items( partno) that haven't been ordered. This is a below diagram

What im trying to achieve is the Full List of partno's from the parts table minus the Partno from the OrderedItems Table. I beleive i would need to do a Left Outter Join.

Like Below

SELECT *
FROM PARTS, ORDEREDITEMS
LEFT OUTER JOIN ordereditems.Partno - parts.Partno;

Still a bit stuck on this one, is this the right track on this?

score 3 · Accepted Answer · answered Jan 04 '18 at 00:03

3

select * 
from parts 
where partno not in ( select partno from ordereditems)

Simply select all that are not in the other table.

select * 
from parts 
where not exists ( select 1 from ordereditems where partno = parts.partno)

would be an alternate way of doing it.

answered Jan 04 '18 at 00:03

Patrick Artner

50,409
9
43
69

Thanks, Worked a treat! – Buzzy Jan 04 '18 at 01:08

score 1 · Answer 2 · answered Jan 04 '18 at 00:10

1

select Parts.*
from Parts left join Ordereditem
on Parts.partno = Ordereditem.partno
where Ordereditem.partno is null

answered Jan 04 '18 at 00:10

kc2018

1,440
1
8
8

We have a winner, folks – Strawberry Jan 04 '18 at 00:55

score 0 · Answer 3 · answered Jan 04 '18 at 00:05

0

try this

select a.partno - b.partno from
(select * from PARTS) as a
left JOIN
(select * from ORDEREDITEMS) as b
on a.Partno = b.Partno

answered Jan 04 '18 at 00:05

Vijunav Vastivch

4,153
1
16
30

you can add the other field using `a.fields and b.fields` – Vijunav Vastivch Jan 04 '18 at 00:06
the important here is you know the foriegn key and the primary key of each and every table like on `a.id = b.id` – Vijunav Vastivch Jan 04 '18 at 00:09

subtraction from multiple tables - sql

3 Answers3