Left Join problem

Results 1 to 2 of 2

Thread: Left Join problem

  1. #1
    Join Date
    Dec 1969

    Default Left Join problem

    I have an OrderLine table with fields like order_number, item_number, order_qty, etc. I have another table Inventory whose primary key is item_number and storeroom, with other fields like stock_level, reorder_qty, etc.<BR>The situation is that an item is located in multiple storerooms but issuing is done only at one storeroom, say MASTER storeroom.<BR>What I am trying to do is retrieve all OrderLine records and the stock level in MASTER storeroom.<BR>I have tried with this SQL statement:<BR>SELECT R.*,I.Stroeroom,I.StockLevel FROM OrderLine R LEFT JOIN Inventory I ON R.ItemNumber=I.ItemNumber WHERE I.Storeroom=&#039;MASTER&#039; OR I.Storeroom Is Null<BR>It does not work because the db engine (I tried Access) makes a join first then filter the where clause. The result is that only OrderLine records matching MASTER storerrom and those no match at all are returned. What I want is filter the Inventory table then make a left join so that all records whether the item is found in MASTER storeroom are returned.<BR>It works out if I create a view of the Inventory tbale filtering out only the MASTER storeroom and left join OrderLine table with this view. But it does not work for me as the issuing storeroom may change and the view need to be removed and created dynamically. (This is the last thing I want to do)<BR>Any idea to help me out is greatly appreciated.

  2. #2
    Join Date
    Dec 1969

    Default Maybe a UNION?

    SELECT R.*,I.Storeroom,I.StockLevel <BR>FROM OrderLine R, Inventory I <BR>WHERE R.ItemNumber=I.ItemNumber <BR>AND I.Storeroom=&#039;MASTER&#039;<BR><BR>UNION<BR><BR >SELECT R.*, NULL, NULL<BR>FROM OrderLine R, Inventory I <BR>WHERE R.ItemNumber=I.ItemNumber <BR>AND I.Storeroom Is Null<BR><BR>ORDER BY ...<BR><BR>?????<BR><BR>In this line:<BR>&nbsp; &nbsp; SELECT R.*, NULL, NULL<BR>you can of course replace the NULL values with any constants of your choice. Maybe:<BR>&nbsp; &nbsp; SELECT R.*, &#039;None&#039;, 0<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts