MySQL - Combine Single Result Query & Multi Result Query into One

Question

I have the 2 following Queries:

This table can have multiple lines per user (many photos) and I just want to return the number/count of photos:

SELECT COUNT(*) FROM photos WHERE photo_description_profanity=1 AND photo_visible=1 AND photo_verified=1 AND userid='1000000002';

This table(s) is only one per user:

SELECT a.userid,a.profile_username,a.profile_gender,a.photo_name,a.photo_verified,b.profile_headline,
       YEAR(DATE_SUB(NOW(), INTERVAL TO_DAYS(a.profile_birthdate) DAY)) AS age,d.city,c.english AS country
FROM login AS a
JOIN `profiles` AS b ON a.userid=b.userid 
JOIN geoCountry AS c ON a.profile_country=c.countryCode
JOIN geoWorld AS d ON a.profile_geo_location=d.pid
WHERE a.userid IN ('1000000002','1000000003','1000000004');

Is it possible to combine these 2 queries?

This is at my limit of ability so any advice would be great :) thx

Please show what you want the results to look like.

Gordon Linoff
– Gordon Linoff

2014-01-29 01:36:38 +00:00
Commented Jan 29, 2014 at 1:36 — Gordon Linoff
– Gordon Linoff, Commented Jan 29, 2014 at 1:36

Craftein · Accepted Answer · 2014-01-29 01:50:44Z

1

SELECT
    a.userid,
    a.profile_username,
    a.profile_gender,
    a.photo_name,
    a.photo_verified,
    b.profile_headline,
    YEAR(DATE_SUB(NOW(), 
    INTERVAL TO_DAYS(a.profile_birthdate) DAY)) AS age,
    d.city,c.english AS country,
    (SELECT COUNT(*) FROM photos WHERE photo_description_profanity=1 AND photo_visible=1 AND photo_verified=1 AND userid= a.userid) AS result_count
FROM login AS a
JOIN `profiles` AS b ON a.userid=b.userid 
JOIN geoCountry AS c ON a.profile_country=c.countryCode
JOIN geoWorld AS d ON a.profile_geo_location=d.pid
WHERE a.userid IN ('1000000002','1000000003','1000000004');

answered Jan 29, 2014 at 1:50

Craftein

7625 silver badges10 bronze badges

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

1 Comment

Adam Over a year ago

Hey thx so much... works great... didn't know I could add a select/query into a select like that... will remember... appreciated :)

Eugen Rieck · Accepted Answer · 2014-01-29 01:54:15Z

1

It is possible. this takes two steps:

adapt the COUNT(*) into a filtering SUM()
plug the source table into the query

.

SELECT a.userid,a.profile_username,a.profile_gender,a.photo_name,a.photo_verified,b.profile_headline,
       YEAR(DATE_SUB(NOW(), INTERVAL TO_DAYS(a.profile_birthdate) DAY)) AS age,d.city,c.english AS country

//Here we insert the count column
,IFNULL(SUM(IF(photo_description_profanity=1 AND photo_visible=1 AND photo_verified=1,1,0)),0) AS photocount

FROM login AS a
JOIN `profiles` AS b ON a.userid=b.userid 
JOIN geoCountry AS c ON a.profile_country=c.countryCode
JOIN geoWorld AS d ON a.profile_geo_location=d.pid

//Here we insert the source table
LEFT JOIN photos ON photos.userid=a.userid

WHERE a.userid IN ('1000000002','1000000003','1000000004')

//Group by users
GROUP BY a.userid

edited Jan 29, 2014 at 1:54

answered Jan 29, 2014 at 1:42

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

2 Comments

Gordon Linoff Over a year ago

This will return one row in MySQL.

Eugen Rieck Over a year ago

@GordonLinoff Thanks! GROUP BY section escaped my mouse in copy-paste mode.

Gordon Linoff · Accepted Answer · 2014-01-29 01:59:47Z

If you want this as an additional column, then cross join it into the results:

select const.cnt, . . . 
from (SELECT COUNT(*) as cnt
      FROM photos
      WHERE photo_description_profanity=1 AND photo_visible=1 AND
            photo_verified=1 AND userid='1000000002'
     ) const cross join
     login AS a
     JOIN `profiles` AS b ON a.userid=b.userid 
     JOIN geoCountry AS c ON a.profile_country=c.countryCode
     JOIN geoWorld AS d ON a.profile_geo_location=d.pid
//Here we insert the source table
     LEFT JOIN photos ON photos.userid=a.userid
WHERE a.userid IN ('1000000002','1000000003','1000000004');

The advantage of using a cross join instead of a nested select query is performance. MySQL will execute the nested select for every row in the return set. In the from clause, it will only be executed once.

Collectives™ on Stack Overflow

MySQL - Combine Single Result Query & Multi Result Query into One

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related