SQL Query date from two tables

Question

I am trying to write a query...

select * from inventoryinfo, purchaseorderiteminfo
where inventoryinfo.qtyinstock < inventoryinfo.minqty
and inventoryinfo.AISTATUS = '1'
and inventoryinfo.category <> 'Noritsu Parts'
and inventoryinfo.itemcode = purchaseorderiteminfo.itemcode
and purchaseorderiteminfo.status = '0'

It returns 10 items

On another table this query shows what items are ordered and on their way

select * from purchaseorderiteminfo
where status = '0'

It returns 8 items

I want to join these two query's to list the 10 items, so I have:

select * from inventoryinfo, purchaseorderiteminfo
where inventoryinfo.qtyinstock < inventoryinfo.minqty
and inventoryinfo.AISTATUS = '1'
and inventoryinfo.category <> 'Noritsu Parts'
and inventoryinfo.itemcode = purchaseorderiteminfo.itemcode
and purchaseorderiteminfo.status = '0'

BUT, it only shows 8 items because the other 2 items do not have an open PO for an order for them. Is there a way to have a list with all 10 items, even 2 of them don't have data in the purchaseorderiteminfo table?

Answer 1

You want to use aleft joinbetween the tables. This gets all rows from the main table, and the matching from the left joined.

select * from inventoryinfo
left join purchaseorderiteminfo on inventoryinfo.itemcode = purchaseorderiteminfo.itemcode and purchaseorderiteminfo.status = '0'
where inventoryinfo.qtyinstock < inventoryinfo.minqty
and inventoryinfo.AISTATUS = '1'
and inventoryinfo.category <> 'Noritsu Parts'

You can also use aliases for the tables to shorten the query a bit:

select * from inventoryinfo i
left outer join purchaseorderiteminfo p on i.itemcode = p.itemcode and p.status = '0'
where i.qtyinstock < i.minqty
and i.AISTATUS = '1'
and i.category <> 'Noritsu Parts'

Also notice that I replaced your old-style implicit joins:

from table1, table2
where table1.key = table2.key

with the explicit syntax used in ANSI standard SQL-92 and later:

FROM table1
LEFT [OUTER] JOIN table2 ON table1.key = table2.key
-- the OUTER keyword is optional in many databases.

See Bad habits to kick : using old-style JOINs for some reasons why you want to do this.