Harrobbed Harrobbed - 4 months ago 6
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

DocEntry U_ZSS_LISTNAME U_ZSS_STARTDATE
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

DocEntry U_ZSS_STORE U_ZSS_STORENAME
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

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'
)
Comments