Join SQL command

[MyPornLife.info]

hey.
i use these SQL command

PHP Code:

    $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:

PHP Code:

    $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:

PHP Code:

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 [DATE]1260613675[/DATE] at [TIME]1260613675[/TIME] ---------------

hey i tried this:

PHP Code:

$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