Union with Null values

[kpetree]

I've got two tables. bmprdstr_sql is an item bill of material table and imordbld_sql is the bill of material attached to an order. I'm working on a query that will show me what items were a part of the original bill of material that are not part of the BOM attached to the order. My thought is to do a full outer join on the two tables but I have to use the order number in my where clause to call the imordbld table (the table with the missing record) so my null value doesn't appear. Any ideas on how to make this work?

My current query..

select comp_item_no as component_item from bmprdstr_sql
full outer join imordbld_sql
on bmprdstr_sql.item_no = imordbld_sql.par_item_no and bmprdstr_sql.comp_item_no = imordbld_sql.item_no
where ord_no like '%123456'

 

[kpetree]

Sorry, the title meant to say "Join with null values" I started with a union and changed it back to this.

 

[Olaf Doschke]

A where clause makes the condition non optional, if it's not met, the record is removed from the result, also the part of the other table(s).

A Join condition only makes it non optional for the join, but the single records without a match remain in the result.

Put that condition into the join condition.

Bye, Olaf.