Add results of one SQL query to another query

Question

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

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 )

can you not do this in development code or must you use SQL? Have you had a look at functions in mysql? — Daniel Casserly
– Daniel Casserly, Commented Oct 15, 2012 at 11:54

Joe G Joseph · Accepted Answer · 2012-10-15 11:56:10Z

1

try this:

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

answered Oct 15, 2012 at 11:56

Joe G Joseph

24.2k5 gold badges58 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vikram Jain · Accepted Answer · 2012-10-15 11:56:05Z

1

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

answered Oct 15, 2012 at 11:56

Vikram Jain

5,6081 gold badge22 silver badges31 bronze badges

Comments

Gidil · Accepted Answer · 2012-10-15 11:57:48Z

1

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)

answered Oct 15, 2012 at 11:57

Gidil

4,1472 gold badges38 silver badges50 bronze badges

Comments

Mahmoud Gamal · Accepted Answer · 2012-10-15 11:59:20Z

1

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

answered Oct 15, 2012 at 11:59

Mahmoud Gamal

80.3k18 gold badges143 silver badges168 bronze badges

Comments

Diego · Accepted Answer · 2012-10-15 12:08:44Z

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

Farfarak · Accepted Answer · 2012-10-16 18:35:40Z

0

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

answered Oct 16, 2012 at 18:35

Farfarak

1,5471 gold badge8 silver badges8 bronze badges

Collectives™ on Stack Overflow

Add results of one SQL query to another query

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related