MySQL problem regarding nested queries (aka. subqueries)

Lemming

Hey guys,

having a problem with an sql statement, and I can't see why. It runs under M$ sql and postgres, and the syntax appears to be in accordance with the MySQL documentation, but I can't figure out wtf is wrong with it

ANy help??

select month, year, status
from claimStatus
where qicID in
(select qicID
from QicClaim
where gmsNum = $gmsNum
and pwd = '$pwd')

I keep getting the following error under MySQL whenver I try to execute the command:

ERROR 1064: You have an error in your SQL syntax near 'select qicID
from QicClaim
where gmsNum = 34985
and pwd = 'ABC123')' at line 3

kayos

Instead of using the IN Statement try EXISTS

IE

select
	month,
	year,
	status 
from
	claimStatus 
where
	EXISTS
	(
	select
		qicID 
	from
		QicClaim 
	where
		gmsNum = $gmsNum 
	and
		pwd = '$pwd'
	and
		QicClaim.qicID = claimStatus.qicID
	) 

Hopefully that will work for you. If not I'll actually look up stuff on MySQL

kayos

dahamsta

What version of MySQL are you running? Only recent versions of MySQL support subselects as far as I know.

adam

Lemming

Originally posted by dahamsta
What version of MySQL are you running? Only recent versions of MySQL support subselects as far as I know.

adam

(\s at command line)

Server version 3.22.32
Protocol version 10


and the "exists" keyword doesn't work in place of "in" - DOH!

amen

have u tried just
select qicID
from QicClaim
where gmsNum = $gmsNum
and pwd = '$pwd'
and see if that works ?

Lemming

Originally posted by amen
have u tried just
select qicID
from QicClaim
where gmsNum = $gmsNum
and pwd = '$pwd'
and see if that works ?

Yup. Works fine.

Snaga

Hey James,

try reading through this thread on phpbuilder....

http://www.phpbuilder.com/forum/read.php3?num=5&id=24660&loc=0&thread=24660

It comes from a similar angle but may not work depending on the contents of claimstatus.

Too tired to put much more effort into it tonight

Lemming

Originally posted by Snaga
Hey James,

try reading through this thread on phpbuilder....

http://www.phpbuilder.com/forum/read.php3?num=5&id=24660&loc=0&thread=24660

It comes from a similar angle but may not work depending on the contents of claimstatus.

Too tired to put much more effort into it tonight

Ta Eric! I'll have a look tomorrow (am just back from the pub .. so needless to say slightly intoxicated )