CODEWITHSUNDEEP
mysqlsql
kayboxa
kayboxa

Reputation: 37

Use results from one SQL query to form part of another query

I have two queries one of which relies on input from another:

Query #1:

SELECT ACCOUNT_ID
FROM ISET.ACCOUNT
WHERE HOLDER='12345'

This will return a list of ACCOUNT_ID associated with holder 12345

I then need to pass these account IDs to the following query to show a list of transactions associated with all the account ids picked up

SELECT TRANS_ID, ACCOUNT_ID, "REF"
FROM ISET.ENTRY
WHERE ACCOUNT_ID='LIST OF ACCOUNT IDS';

How would I find the most efficient way of doing this?

Thanks for looking.

Upvotes: 2

Views: 128

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use IN:

SELECT TRANS_ID, ACCOUNT_ID, "REF"
FROM ISET.ENTRY
WHERE ACCOUNT_ID IN (SELECT ACCOUNT_ID
                     FROM ISET.ACCOUNT
                     WHERE HOLDER = '12345'
                    );

Upvotes: 2

waka
waka

Reputation: 3407

Just use a subselect combined with the IN statement.

SELECT TRANS_ID, ACCOUNT_ID, "REF"
FROM ISET.ENTRY
WHERE ACCOUNT_ID IN (SELECT ACCOUNT_ID
                    FROM ISET.ACCOUNT
                    WHERE HOLDER='12345');

Upvotes: 3

kzharas210
kzharas210

Reputation: 500

SELECT TRANS_ID, ACCOUNT_ID, 'REF'
  FROM ISET.ENTRY
 WHERE ACCOUNT_ID IN (SELECT ACCOUNT_ID
                        FROM ISET.ACCOUNT
                       WHERE HOLDER='12345');

Upvotes: 3

Related Questions