Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Union with Null values

Union with Null values

Union with Null values

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..

CODE -->

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' 

RE: Union with Null values

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

RE: Union with Null values

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close