Join SQL command

[MyPornLife.info]

hey.
i use these SQL command
$a = $db->query_first("SELECT `IDs` FROM " . TABLE_PREFIX . "second WHERE pID = $pID");
$b = $a['IDs']; // it returns like 7,9,15,16
$query = $db->query_read("SELECT * FROM " . TABLE_PREFIX . "first WHERE ID IN ($b)");

its working perfectly.

but i want to use a single query instead of two
i tried this:
$query = $db->query_read("SELECT * FROM " . TABLE_PREFIX . "first AS first
LEFT JOIN " . TABLE_PREFIX . "second AS second
WHERE first.ID IN (second.IDs) AND second.pID = $pID");
not working.showing error

plz help me

[Lynne]

Perhaps something like:
LEFT JOIN " . TABLE_PREFIX . "second AS second ON (first.ID = second.IDs)

It's not clear to me whether there is an ID field in both tables (or whether it really is IDs in second), but you need to say what the two tables are joined ON - what the relationship is between the two tables.

[MyPornLife.info]

field "ID" is in table: first (its an INT field)
field "IDs" is in table: second (its a VARCHAR field)

so "ID" & "IDs" two different field in deferent table.

"ID" contains a single unique number
but "IDs" contain some id. i.e- 14,15,16,17,118

so i think ON is not a good idea...coz they never be equal.
thats why IN must be used.

plz read my both codes of first post very carefully again...so it will be clear to u
& plz help me

& sorry for the confusing post :)

--------------- Added 1260613675 at 1260613675 ---------------

hey i tried this:
$query = $db->query_read("SELECT * FROM " . TABLE_PREFIX . "first AS first
LEFT JOIN " . TABLE_PREFIX . "second AS second
ON (first.ID IN (second.IDs)) WHERE second.pID = $pID");

its not showing error but incomplete result.
i mean it returns just only one table although it shud show more as there r more row according to condition.

[Lynne]

It could be that you want a RIGHT JOIN instead of a LEFT JOIN.

[MyPornLife.info]

not working. same result