query question

[burnist]

I want to select a row from a table thats contains a list of ids which is simple enough but i want to then take each id from that row (1 per coloum) and use it to select roms in another table. I cant think of anyway to do this as there would be new colums added to the first table quite frequently.

[Dean C]

You can do queries within queries:

e.g. SELECT * FROM table WHERE(SELECT ids FROM table2);

I believe ^^

[burnist]

$useritemsquery = $DB_site->query("SELECT id,name,icon FROM items WHERE id =('SELECT * FROM items_users WHERE userid=$bbuserinfo[userid]') ORDER BY id ASC");
while($useritems = $DB_site->fetch_array($useritemsquery)){
eval("\$showusersitem .= \"".fetch_template("itemshop_showuseritems")."\";");
}

Thats what i've go so far and i think what you ment works but i think it joins all the id's together into one big id and then the first query cant find the row and wont return it so the eval never happens

[Christine]

You can do queries within queries:

e.g. SELECT * FROM table WHERE(SELECT ids FROM table2);

I believe ^^
Dean,

I think that isn't available until MySQL 5 (or is it 4.1)?

[burnist]

^ crap i only run 4.0.18 :(

[Dean C]

I'm pretty sure I read it in the mysql 4 manual :)

[Christine]

Gotcha.

I had to dig it back up, because I didn't recall the specifics but it stuck out to me that 'I can't do this yet'.

Apparently, it isn't a matter of can't, it is a matter of how (and that is what changes in 4.1) For MySQL versions prior to 4.1, most subqueries can be successfully rewritten using joins and other methods.

http://dev.mysql.com/doc/mysql/en/Subqueries.html

http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html

Glad I saw this thread!!

[burnist]

So basicly im looking for something like this ??


$DB_site->query("SELECT id,name,icon FROM items
LEFT JOIN items_users ON items.id=items_users.*
WHERE items_users.userid IS $bbuserinfo[userid]; ORDER BY items.id ASC");