Page 1 of 1

subquery problem

Posted: Thu Nov 27, 2003 4:33 am
by valen53

Code: Select all

<?php

$query = "Select * from fee where fee_id <> ANY (select fee_id from yearly_fee where group_id = '$edited') "; 					
$result = mysql_query($query) ; 

?>
above coding i get from
http://www.mysql.com/doc/en/ANY_IN_SOME ... ries.html

but no matter how i try it, it still error.
anyone can help me? thank u

Posted: Thu Nov 27, 2003 4:38 am
by infolock
what is the error code you are getting.

Posted: Thu Nov 27, 2003 4:43 am
by twigletmac
Which version of MySQL are you using?

Mac

Posted: Thu Nov 27, 2003 7:25 pm
by valen53
thank for reply

i use mysql 3.23.25
i think should be version problem, i thought i use mysql 4.0 above..
So it should be use mysql 4.1 , right ?

But if i change the query to
code 1

Code: Select all

<?php
$query3 = "SELECT fee.* FROM fee LEFT JOIN yearly_fee ON fee.fee_id = yearly_fee.fee_id where group_id = '$edited' and fee.fee_id IS NULL " ; 
?>
is it same meaning to
code2

Code: Select all

<?php
$query = "Select * from fee where fee_id <> ANY (select fee_id from yearly_fee where group_id = '$edited') ";                 
?>
the code1 i used, nothing was select out.

Posted: Sat Nov 29, 2003 5:20 pm
by JAM
it is a version problem. subqueries like those only work in 4.0 and above (even 4.1 imho).

In your code, you where missing the referal to the table using in the where clause; group.id.

Code: Select all

select 
    fee.* 
from 
    fee 
    left join yearly_fee on fee.fee_id = yearly_fee.fee_id 
where 
    fee.group_id = '$edited' and  -- missed fee. here (or yearly_fee.)
    fee.fee_id is null

Posted: Sun Nov 30, 2003 8:47 pm
by valen53
thank for reply

But even i added the "yearly_fee.group_id", nothing was select out also.
quite confuse now..

Code: Select all

<?php
$query3 = "SELECT fee.* FROM fee LEFT JOIN yearly_fee ON fee.fee_id = yearly_fee.fee_id where yearly_fee.group_id = '$edited' and fee.fee_id IS NULL" ; 
?>

Posted: Sun Nov 30, 2003 10:28 pm
by JAM
Looks correct.
Is $edited set to something? Are there any yearly_fee.fee_id that is NULL (as you link the tables using that).
To debug abit better, strip out the entire where-clause, then add each part manually to see where it breaks.
Hope it helps.