CODEWITHSUNDEEP
mysqlselectjoinsubquery
Per
Per

Reputation: 35

How can I join these two complex queries?

I have two queries, and they are quite comlpex, so I can't seem to figure out how to join them.

I want to find the resulting set when Q1.notAnyFewID = Q2.FBID from the two queries

Q1:

SELECT DISTINCT notifications.`receiver` AS notAnyFewID
FROM notifications
JOIN 
(SELECT notifications.`ref` AS notRef, notifications.`receiver` AS recI
    FROM notifications
    WHERE notifications.`ref`='tooFewLandings') AS c
ON notifications.`receiver`=c.recI
WHERE notifications.`receiver`!=c.recI

Q2:

SELECT DISTINCT R2PProfiles.id AS r2pID, R2PProfiles.`facebookID` AS FBID
FROM R2PProfiles
LEFT JOIN
      (SELECT COUNT(*) AS Landings, R2PProfiles.facebookID, R2PProfiles.id
            FROM pageTrack         
            JOIN (R2PProfiles)
        ON (pageTrack.inputRefNum = R2PProfiles.id)
WHERE pageTrack.ref='getProfile-Land' AND R2PProfiles.published=2 AND R2PProfiles.`createTime`< NOW()- INTERVAL 24 HOUR GROUP BY R2PProfiles.id) AS h
USING (id) WHERE (Landings < 20)

When trying to combine them I seem to always get messed up in join or sub-selects or "using" or where and how to get the new where correct.

What is the best approach for making one of the queries compare against the result of the other one?

Upvotes: 0

Views: 121

Answers (1)

Barmar
Barmar

Reputation: 781726

Just put the two queries as subqueries in a JOIN

SELECT notAnyFewID, r2pID
FROM (SELECT DISTINCT notifications.`receiver` AS notAnyFewID
        FROM notifications
        JOIN 
        (SELECT notifications.`ref` AS notRef, notifications.`receiver` AS recI
            FROM notifications
            WHERE notifications.`ref`='tooFewLandings') AS c
        ON notifications.`receiver`=c.recI
        WHERE notifications.`receiver`!=c.recI) AS q1
JOIN (SELECT DISTINCT R2PProfiles.id AS r2pID, R2PProfiles.`facebookID` AS FBID
        FROM R2PProfiles
        LEFT JOIN
              (SELECT COUNT(*) AS Landings, R2PProfiles.facebookID, R2PProfiles.id
                    FROM pageTrack         
                    JOIN (R2PProfiles)
                ON (pageTrack.inputRefNum = R2PProfiles.id)
        WHERE pageTrack.ref='getProfile-Land' AND R2PProfiles.published=2 AND R2PProfiles.`createTime`< NOW()- INTERVAL 24 HOUR GROUP BY R2PProfiles.id) AS h
        USING (id) WHERE (Landings < 20)) AS q2 
ON q1.notAnyFewID = q2.FBID

Upvotes: 3

Related Questions