mysql select from Table A (on conditions) where not in Table B (on same conditions)

I am having a devil of a time on this one. I have been reading and trying different solutions but none are working for me. I have created a raffle system, and need to select users, who meet all conditions, that have not been previously selected before based on the same conditions.

TableA fields, user has checked in to location:

chID | ceID | cID  | crID | clID | user_ID | chTime   | chDate
1      1135   1001   1001   1163   8213451   17:21:30   2022-04-21
2      1135   1001   1001   1163   8213452   17:22:30   2022-04-21
3      1135   1001   1001   1163   8213453   17:23:30   2022-04-21
4      1135   1001   1001   1163   8213454   17:24:30   2022-04-21
5      1135   1001   1001   1163   8213455   17:25:30   2022-04-21

6      1155   1021   1001   1183   9213451   17:21:30   2022-04-21
7      1155   1021   1001   1183   9213452   17:22:30   2022-04-21
8      1155   1021   1001   1183   9213453   17:23:30   2022-04-21
9      1155   1021   1001   1183   9213454   17:24:30   2022-04-21
10     1155   1021   1001   1183   9213455   17:25:30   2022-04-21

11     1136   1002   1001   2163   8213451   17:21:30   2022-04-21
12     1136   1002   1001   2163   8213452   17:22:30   2022-04-21
13     1136   1002   1001   2163   8213453   17:23:30   2022-04-21
14     1136   1002   1001   2163   8213454   17:24:30   2022-04-21
15     1136   1002   1001   2163   8213455   17:25:30   2022-04-21

NOTE: 1-5 & 11-15 are same users, but group category IDs are different between them

TableB fields, user was previously selected as a winner:

bID | ceID | cID  | crID | clID | user_ID | chTime   | chDate
1     1135   1001   1001   1163   8213451   17:21:30   2022-04-21
2     1155   1021   1001   1183   9213454   17:24:30   2022-04-21
3     1136   1002   1001   2163   8213453   17:23:30   2022-04-21

Table A stores users that have checked into specific groups. Table B stores the user when a user from that group is selected for a raffle.

I need to be able to select from Table A based on group checkin info that doesn't exist in TableB based on same group checkin info.

For this example, I need to select a user where: ceID=1135, cID=1001, crID=1001, clID=1163 AND chDate=2022-04-21 That does not exist in Table B based on the same criteria:

// This returns user from wrong group:
select ch.user_ID FROM TableA ch 
  WHERE NOT EXISTS (
    select 1 FROM tableB cr 
      WHERE ch.cID=1001 AND ch.cID=cr.cID
        AND ch.crID=1001 AND ch.crID=cr.crID
        AND ch.clID=1163 AND ch.clID=cr.clID
        AND ch.ceID=1135 AND ch.ceID=cr.ceID
        AND ch.user_ID=cr.user_ID
        AND ch.chDate='2022-04-21' AND ch.chDate=cr.chDate
  ) 
  Group by ch.user_ID 
  ORDER BY RAND() LIMIT 1

// This returns nothing:
SELECT ch.* FROM tableA ch 
  left join tableB cr on ch.user_ID=cr.user_ID 
  WHERE ch.cID=1001 AND cr.cID=1001
    AND ch.crID=1001 AND cr.crID=1001  
    AND ch.clID=1163 AND cr.clID=1163  
    AND ch.ceID=1135 AND cr.ceID=1135  
    AND ch.chDate='2022-04-21' AND cr.chDate='2022-04-21'
    AND cr.user_ID IS NULL  
    GROUP BY ch.user_ID 
    ORDER BY RAND() LIMIT 1
  ;

I have gone through several different iterations of both but to no avail.

Solution

I would write it this way:

SELECT ch.* FROM tableA AS ch 
LEFT OUTER JOIN tableB AS cr 
  ON ch.user_ID=cr.user_ID 
  AND cr.cID=1001 
  AND cr.crID=1001 
  AND cr.clID=1163 
  AND cr.ceID=1135 
  AND cr.chDate='2022-04-21'
WHERE ch.cID=1001
  AND ch.crID=1001
  AND ch.clID=1163
  AND ch.ceID=1135
  AND ch.chDate='2022-04-21'
  AND cr.user_ID IS NULL  
GROUP BY ch.user_ID 
ORDER BY RAND() LIMIT 1;

If you want to put conditions on the cr table in that outer join, put the conditions in the ON clause. If you put the conditions in the WHERE clause, it means the columns must be non-NULL which means there must be a match in the join. But you're looking for cases where no row with those conditions is found to satisfy the join.

By the way, I'm not sure about the GROUP BY. Unless user_id is the primary or unique key of tableA, then the other columns aren't functionally dependent and the grouping should be an error. But if the column is a primary or unique key, then the grouping is a no-op, so I'm not sure why it's there.