mysql subqueries

any ideas to work this thing out?

[Zipper]

first of all, isn't there a forum for db issues? anyway, i'm trying to get this subquery work but it always throws a 1064 error.

'SELECT * FROM `table1` WHERE `column1` = ANY (SELECT `column1` FROM `table2` WHERE `column2` = X)';

the subquery returns multiple rows, and column1 on both tables are integers. got any idea?

[Zipper]

oh, I just figured it's to do with the version. will have to wait till 4.1 stablizes. in the meantime, isn't there anyway to make this statement more efficient? that's leaving subqueries and looping through the first record set to query the second for each result.

[jatar_k]

hmm, I played around with this a bit but not totally sure if I am right. What about something like

select * from table1 a, table2 b where a.column1 = b.column1 and b.column2 = X;

you may have to list the fields you want to select explicitly though. I am not convinced that will get you exactly what you want though.

You may just be better to do 2 queries

SELECT `column1` FROM `table2` WHERE `column2` = X
then put the returned values into an array and use
SELECT * FROM `table1` WHERE `column1` in ($myarray);

[Zipper]

the second method u stated is what I'm already doing. I'm not using an array though. just looping thru the first recordset to query the second as I said ealier. This is not efficient at all, considering the no. of queries/page. I did contact a mysql tech, who said to tryout the alpha version. I'm quite skeptic abt it. with all the changes that would need to be done and expecting to upgrade again once the final version is out. i'll stick to this.. I will also give a shot at your first method, just in case. thanks ;)

btw, if any one else has any means of tweaking this, pls let me know.

[Zipper]

whoa! guess what,

select * from table1, table2 where table1.col1 = table2.col1 AND table2.col1 = X

works!
I used to do this all the time when I was using ASP and SQL server. wonder why I didn't think abt it with PHP/MySQL. the best part is that i won't need an upgrade now..lol ;)
thanks man..

[jatar_k]

>>works

wow, I wasn't sure I quite had a grasp of the structure you were alluding to, glad it worked.