Harrobbed Harrobbed - 1 year ago 59
SQL Question

How to Query the result set of another query in SQL

I have a two tables. One is the 'List' table where the primary key is DocEntry

17 WKLY_CC_F_06_22_2016 2016-06-22 00:00:00.000
18 WKLY_CC_F_06_22_2016 2016-06-22 00:00:00.000

Another table is the stores table

17 01 General Warehouse
17 02 West Cost Warehouse
18 02 West Cost Warehouse
18 04 Consignmentl Warehouse

These lists are assigned to different stores where DocEntry is the foreign key from the List table

The thing is I want a result which I can query to get all the lists assigned to a particular store 'U_ZSS_STORENAME'.

That is all the lists assigned to store 'West Cost Warehouse' in this case it would be the lists with DocEntry '17' and '18'

Answer Source

This is a simple INNER JOIN:

Select  L.*
From    List        L
Join    Stores      S   On  S.DocEntry = L.DocEntry
Where   S.U_ZSS_STORENAME = 'West Cost Warehouse'

Another way to do this is with an EXISTS statement:

Select  *
From    List        L
Where Exists
    Select  *
    From    Stores  S
    Where   S.DocEntry = L.DocEntry
    And     S.U_ZSS_STORENAME = 'West Cost Warehouse'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download