nested select mysql

Question

hi i want write this sql select statment in mysql v 3.23

select * from radacct where username in (select username from radcheck where Attribute= 'max-weekly-session');

but say

ERROR 1064: You have an error in your SQL syntax near 'select username from radcheck where Attribute= 'max-weekly-session')' at line 1

in mysql v 5 this is ok but what can i do in v 3.23? thank you

Answer 1

try this:

select a.* 
from radacct a 
join radcheck b on a.username = b.username
where b.Attribute = 'max-weekly-session'

Answer 2

Full standards-compliant support for subqueries was added in 4.1. Prior to that, you had to use JOINs and other workarounds to achieve the same effect.

See 12.2.8.11. Rewriting Subqueries as Joins for Earlier MySQL Versions

Answer 3

select * 
from radacct ra
inner join radcheck on ra.username = rc.username
where rc.Attribute = 'max-weekly-session'

Answer 4

nested selects are not available in MySQL version < 4.1

following will work

select radacct.* 
from radacct inner join radcheck on radacct.username = radcheck.username 
where radcheck.Attribute= 'max-weekly-session'