subquery problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
valen53
Forum Contributor
Posts: 137
Joined: Tue Aug 27, 2002 9:29 am

subquery problem

Post 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
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

what is the error code you are getting.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Which version of MySQL are you using?

Mac
valen53
Forum Contributor
Posts: 137
Joined: Tue Aug 27, 2002 9:29 am

Post 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.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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
valen53
Forum Contributor
Posts: 137
Joined: Tue Aug 27, 2002 9:29 am

Post 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" ; 
?>
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
Post Reply