CODEWITHSUNDEEP
mysqlsql
sHa Xahid
sHa Xahid

Reputation: 101

Add results of one SQL query to another query

I don't now much about SQL queries, I have a problem

SELECT PERSON_REF 
FROM   IMAGES 
WHERE  PERSON_REF IN (SELECT _PERSON_ID 
                      FROM   PERSONS 
                      WHERE  REGION_REF = (SELECT _REGION_ID 
                                           FROM   REGIONS 
                                           WHERE  REGION_ABB = "EU")) 
GROUP  BY PERSON_REF 
HAVING Count(PERSON_REF) >= 3

this query gives my result like

2  
4  
5  
6

Now I want to use that result in an other query, how to do that, Do I need to create loop or is there any other way

SELECT PERSON_REF 
FROM   IMAGES 
WHERE  EFFECT_REF = 2 
       AND PERSON_REF IN ( 2, 4, 5, 6 )

Upvotes: 4

Views: 3097

Answers (6)

Farfarak
Farfarak

Reputation: 1517

Just add AND to the first predicate and specify another one.

SELECT PERSON_REF FROM IMAGES WHERE PERSON_REF IN (SELECT _PERSON_ID FROM PERSONS WHERE REGION_REF = (SELECT _REGION_ID FROM REGIONS WHERE REGION_ABB = "EU")) AND EFFECT_REF=2 GROUP BY PERSON_REF HAVING Count(PERSON_REF) >= 3

Upvotes: 0

Diego
Diego

Reputation: 36126

there are several ways you can do that. You can use the IN example as was already ansered but it will work only if you need only one column (which is your case, I know, but it is good to know what you can do if you face a situation where you need more that oncolumn)

The simples would be wrap your query into parentesys and give it an alias so you can use it like if it were a table:

(SELECT PERSON_REF 
FROM   IMAGES 
WHERE  PERSON_REF IN (SELECT _PERSON_ID 
                      FROM   PERSONS 
                      WHERE  REGION_REF = (SELECT _REGION_ID 
                                           FROM   REGIONS 
                                           WHERE  REGION_ABB = "EU")) 
GROUP  BY PERSON_REF 
HAVING Count(PERSON_REF) >= 3 ) MY_ALIAS

then yo ucan join with your other query. This is usually not a good approach though.

The best apporach, its to use Common Table Expressions (CTEs) so you would do something like this:

with MY_FIRST_QUERY as(
SELECT PERSON_REF 
    FROM   IMAGES 
    WHERE  PERSON_REF IN (SELECT _PERSON_ID 
                          FROM   PERSONS 
                          WHERE  REGION_REF = (SELECT _REGION_ID 
                                               FROM   REGIONS 
                                               WHERE  REGION_ABB = "EU")) 
    GROUP  BY PERSON_REF 
    HAVING Count(PERSON_REF) >= 3 
)
select MY_FIRST_QUERY.PERSON_REF

and the you join it wth whatever you need

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

JOIN the three tables Images, Persons and Regions instead of these INs and subqueries:

 SELECT i.Person_Ref 
 FROM Images i
 INNER JOIN Persons p ON i.Person_Ref = p.Person_Ref
 INNER JOIN Regions r ON p.Region_Ref = r.Region_Ref
 WHERE r.Region_Abb = "EU"
   AND i.Effect_Ref = 2 
 GROUP BY i.Person_Ref 
 HAVING COUNT(i.Person_Ref) >= 3

Upvotes: 1

Gidil
Gidil

Reputation: 4137

Try this:

SELECT PERSON_REF 
FROM   IMAGES 
WHERE  EFFECT_REF = 2 
       AND PERSON_REF IN (SELECT PERSON_REF 
                          FROM   IMAGES 
                          WHERE  PERSON_REF IN (SELECT _PERSON_ID 
                                                FROM   PERSONS 
                                                WHERE 
                                 REGION_REF = (SELECT _REGION_ID 
                                               FROM   REGIONS 
                                               WHERE 
                                 REGION_ABB = "EU")) 
                          GROUP  BY PERSON_REF 
                          HAVING Count(PERSON_REF) >= 3)

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

select Person_Ref from Images where Effect_Ref = 2 and Person_Ref IN 
( your first query which gives 2 4 5 6)

Upvotes: 1

Vikram Jain
Vikram Jain

Reputation: 5588

select Person_Ref from Images  where Person_Ref IN ( 
Select _Person_ID from Persons where
Region_Ref in (select _Region_ID from Regions where Region_Abb = "EU"))
and EFFECT_REF = 2 
group by Person_Ref HAVING COUNT(Person_Ref) >= 3

Upvotes: 1

Related Questions