Mysql Sql Query

[Dave_cha]

I've just moved some tables to a MYSQL backend and have to rewrite an SQL query as the version of MYSQL I'm running doesn't support subqueries.

Can anyone tell me how to work around this;

SELECT TBL_Employee_list.[USERID], [Surname] FROM TBL_Employee_list
WHERE TBL_Employee_list.[USERID] Not In (Select TBL_Leave_allowance.[USERID] from TBL_Leave_allowance)

If I use a join between two tables it only takes into account data where USERID and EMPID are alike. I'm trying to isolate all records in TBL_Employee_list records which aren't in TBL_Leave_allowance.

Thanks, Dave

 

[FoFa]

If you use an outer join, and check for a null value on the other table where there should be a value, then that record doesn't exist:

SELECT TBL_Employee_list.[USERID], [Surname]
FROM TBL_Employee_list EL
LEFT JOIN TBL_Leave_allowance LA ON EL.USERID = LA.USERID
WHERE LA.USERID IS NULL

 

[Dave_cha]

Thanks FoFa....sometimes you just can't see the wood for the trees.