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.