CODEWITHSUNDEEP
sql-server
Eric Becigneul
Eric Becigneul

Reputation: 11

SQL query based on list from another query

I am trying to build a query that will generate a list of records based on the results of a very similar query.

Here are the details and examples

Query 1: Generate a list if part #'s in a specific location of the warehouse.

Query 2: Use the list of part #'s generated in #1 to show all locations for the list of part #'s, assuming they will be in both the location specified in #1 and other locations.

Query 1 looks like this:

Select
    ItemMaster.ItemNo, BinInfo.BIN, ItemDetail.Qty, ItemDetail.Whouse_ID
From
    ((ItemDetail
Left Join 
    ItemMaster on ItemMaster.ID=ItemDetail.Item_ID)
Left Join 
    BinInfo on BinInfo.ID = ItemDetail.Bin_ID)
Where  
    ItemDetail.Whouse_ID  = '1'
    And BinInfo.Bin = 'VLM';

Query 2 needs to be almost identical except the ItemMaster.ItemNo list will come from query #1.

Any help here would be great. I don't know if I need to learn Unions, Nested Queries, or what.

Upvotes: 1

Views: 2050

Answers (1)

Z .
Z .

Reputation: 12837

  1. make sure that your first query returns the list of ids that you need.

  2. then write the second query with the WHERE id IN (...) syntax:

    SELECT * FROM table1 WHERE id IN
    (SELECT id FROM table2 WHERE...) -- first query

Upvotes: 2

Related Questions