CODEWITHSUNDEEP
mysqlsql
Robert Lilly
Robert Lilly

Reputation: 13

How to use result set from one query in second query on same table

I need to query a table once to obtain ID numbers based on a given condition, then I need to use those ID numbers to retrieve another set of values, all on the same table. Here's the first query:

SELECT post_id
FROM ih_postmeta
WHERE meta_key = '_edd_discount_uses'
AND meta_value > '0'

This works fine to retrieve the post_id numbers. Now I need to run the equivalent of this query:

SELECT meta_value
FROM ih_postmeta
WHERE post_id = '1088' 
    AND meta_key = '_edd_discount_code'

This works, except I need to able to loop through the values obtained from the first query and not have WHERE post_id = hard coded.

Here's a sample from the table in question for a specific post_id:

|meta_id|post_id|    meta_key      |meta_value
|1822   |1088   |_edd_discount_uses|8
|1823   |1088   |_edd_discount_code|ls100

Upvotes: 1

Views: 1778

Answers (1)

Hart CO
Hart CO

Reputation: 34774

You can use IN with a subquery to get what you want:

SELECT meta_value
FROM ih_postmeta
WHERE post_id IN (SELECT post_id
                  FROM ih_postmeta
                  WHERE meta_key = '_edd_discount_uses'
                  AND meta_value > '0') 
    AND meta_key = '_edd_discount_code'

Upvotes: 3

Related Questions