CODEWITHSUNDEEP
sql
Harobed
Harobed

Reputation: 167

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'

Upvotes: 0

Views: 21

Answers (1)

Siyual
Siyual

Reputation: 16917

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

Upvotes: 1

Related Questions