My below query works, but there are two things I want to get from the query that I don't know how to do.

1. How to tell which LEFT JOIN the final returned row is coming from?
2. Is it possible to also return the total count from each LEFT JOIN?

SELECT * FROM (
  (SELECT ch.user_ID, ch.clID FROM clubHistory AS ch 
     LEFT OUTER JOIN clubRaffleWinners AS cr ON 
     ch.user_ID = cr.user_ID  
     AND cr.cID=1157 
     AND cr.rafID=18  
     AND cr.chDate1 = '2022-06-04' 
   WHERE  ch.cID=1157  
     AND ch.crID=1001   
     AND ch.ceID=1167  
     AND ch.chDate = '2022-06-04' 
     AND cr.user_ID IS NULL 
   GROUP BY ch.user_ID ) 
UNION ALL 
  (SELECT cu.user_ID, cu.clID FROM clubUsers AS cu 
    LEFT OUTER JOIN clubRaffleWinners AS cr1 ON 
      cu.user_ID = cr1.user_ID  
      AND cr1.cID=1157 
      AND cr1.rafID=18  
      AND cr1.chDate1 = '2022-06-04' 
    WHERE  cu.cID=1157  
      AND cu.crID=1001   
      AND cu.ceID=1167  
      AND cu.calDate = '2022-06-04' 
      AND cr1.user_ID IS NULL 
    GROUP BY cu.user_ID )
) as winner ORDER BY RAND() LIMIT 1 ;

In my two left join select statements I tried:

  (SELECT ch.user_ID as chUserID, ch.clID FROM clubHistory AS ch 
   and
  (SELECT cu.user_ID as cuUserID, cu.clID FROM clubUsers AS cu 

But every single result, after dozens and dozens of tries comes back a user_ID or chUserID. When I remove the ORDER BY RAND() LIMIT 1 - the only two columns that come back are user_ID, clID or chUserID, clID even though the combined results is the full list of both tables. Is this even possible?

And #2 above, is it possible to extract the total counts from each LEFT JOIN with and with out the final order by rand() limit 1 ???

CodePudding user response：

For 1 add an extra column containing a value that identifies which subquery of the UNION it is.

SELECT * FROM (
  (SELECT 'history' AS which, ch.user_ID, ch.clID FROM clubHistory AS ch 
     LEFT OUTER JOIN clubRaffleWinners AS cr ON 
     ch.user_ID = cr.user_ID  
     AND cr.cID=1157 
     AND cr.rafID=18  
     AND cr.chDate1 = '2022-06-04' 
   WHERE  ch.cID=1157  
     AND ch.crID=1001   
     AND ch.ceID=1167  
     AND ch.chDate = '2022-06-04' 
     AND cr.user_ID IS NULL 
   GROUP BY ch.user_ID ) 
UNION ALL 
  (SELECT 'users' AS which, cu.user_ID, cu.clID FROM clubUsers AS cu 
    LEFT OUTER JOIN clubRaffleWinners AS cr1 ON 
      cu.user_ID = cr1.user_ID  
      AND cr1.cID=1157 
      AND cr1.rafID=18  
      AND cr1.chDate1 = '2022-06-04' 
    WHERE  cu.cID=1157  
      AND cu.crID=1001   
      AND cu.ceID=1167  
      AND cu.calDate = '2022-06-04' 
      AND cr1.user_ID IS NULL 
    GROUP BY cu.user_ID )
) as winner ORDER BY RAND() LIMIT 1 ;

Please only ask one question at a time.