Get records NOT in a subquery 1

[Griffyn]

Hi all,

This is the SQL that works in Jet (MS Access)

SELECT M.CODE, M.NAME
FROM ARMASTER M LEFT JOIN (SELECT DEBTOR FROM ARDEBTAG WHERE TAG_CODE='INACTIVE') T ON M.CODE = T.DEBTOR
WHERE T.DEBTOR Is Null

To summarise, table ARDEBTAG contains zero->many records per record in ARMASTER, one of which may have TAG_CODE='INACTIVE'. I want to return records from ARMASTER which do not have a matching record in ARDEBTAG that has TAG_CODE='INACTIVE'.

Now I have to do the same thing using the CONNX ODBC driver, which doesn't appear to support dynamic tables, even though during it's errors it appropriately summarises the inner SELECT statement as '(cnxsubquery)'. It seems that it's not properly associating the alias T with the subquery, and I've tried using AS there as well.

Presuming I can't do it that way, is there a way to do what I want in a single SQL statement without using the subquery?

Many thanks.

 

[r937]

try this --

select M.CODE
     , M.NAME
  from ARMASTER M 
left outer
  join ARDEBTAG D
    on (
       M.CODE = D.DEBTOR
   and D.TAG_CODE = 'INACTIVE'
       )  
 where D.DEBTOR is null

if that doesn't work, then further ANSI SQL is probably not going to help, you should ask the question in one of the Access forums

r937.com | rudy.ca

 

[PHV]

2 another ways:
SELECT M.CODE, M.NAME
FROM ARMASTER M
WHERE M.CODE NOT IN (SELECT DEBTOR FROM ARDEBTAG WHERE TAG_CODE='INACTIVE')

SELECT M.CODE, M.NAME
FROM ARMASTER M
WHERE NOT EXISTS (SELECT * FROM ARDEBTAG WHERE DEBTOR = M.CODE AND TAG_CODE='INACTIVE')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Griffyn]

I appreciate your thoughts r937, but PHV got the gold. That first SQL works great. Thanks!