SQL problem - getting it into MySQL speak 1

[LittleSmudge]

I know how I'd do this in Access or other RDBs but I can't get it to work in MySQL

For
tblA
AId PK for tblA

tblB
BId PK for tblB
ARef FK to tblA

I need
All records from tblA WHERE there is NOT a record in tblB corresponding to tblA INNER JOIN tblB and tblB.DateRef = 7

( In other words a simple 'missing records' list )


In other dbs this would be

SELECT *
FROM tblA
WHERE tblA.AId
NOT IN ( SELECT tblA.AId
FROM tblA INNER JOIN tblB
ON tblA.AId = tblB.ARef
WHERE tblB.DateRef = 7 )


However, I get a
"You have an error in your SQL syntax near 'SELECT tblA.AId FROM tblA INNER JOIN tblB ON ' at line 1"

error message


It seems that MySQL doesn't like "IN ( SELECT .. .."

So what can I use instead ?





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[r937]

mysql doesn't do subqueries prior to the current release (4.1)

you can do a simple "not exists" like this --

select tblA.*
  from tblA 
left outer
  join tblB 
    on tblA.AId = tblB.ARef 
   and tblB.DateRef = 7
 where tblB.ARef is null

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[hoinz]

Hi,

do you really need an inner join?
I didn't try, but what about:

WHERE tblA.AId
NOT IN ( SELECT ARef
FROM tblB
WHERE DateRef = 7 )

hth

 

[hoinz]

ok,

r937, I was too slow,
and I didn't know about subqueries prior to the current release.

 

[LittleSmudge]

GREAT r937 that does it.

Putting the "and tblB.DateRef = 7" into the ON clause instead of in the WHERE clause does the trick.


Thanks r937 - much appreciated. *







G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.