use a sql select statement to get parameters for 2nd select statement

Question

Hi there, I am trying to write a sql statement that

I have 2 tables Store & StoreTransactions. My first select command looks like

SELECT [StoreID],[ParentStoreID] 
FROM Store

Very simple stuff. How do I take the returned StoreID's and use them for my 2nd select statement?

SELECT [StoreTransactionID],[TransactionDate],[StoreID]
FROM StoreTransactions
WHERE StoreID = returned values from the above query

Any help would be great!

Answer 1

SELECT [StoreTransactionID],[TransactionDate],[StoreID]
FROM StoreTransactions
WHERE StoreID in (select StoreId from Store)

This is known as a nested select, or inner select.

Answer 2

Couple of other ways of doing it...

SELECT [StoreTransactionID],[TransactionDate],[StoreID]
FROM StoreTransactions st
WHERE EXISTS
(
SELECT * 
FROM Store s
WHERE s.[StoreID] = st.[StoreID]
)

And

SELECT [StoreTransactionID],[TransactionDate],st.[StoreID]
FROM StoreTransactions st
INNER JOIN Store s ON s.[StoreID] = st.[StoreID]

Answer 3

An alternative way of writing it is to use an INNER JOIN

SELECT [StoreTransactionID],[TransactionDate],[StoreTransactions.StoreID]
FROM StoreTransactions INNER JOIN Store ON StoreTransactions.StoreID=Store.StoreID

This may be more efficient in some RDBMSs.

If your Store query also includes a WHERE clause, you can just add that to the query above.