mysqlwhere-clausemultiple-tablesin-subquery

MySQL WHERE <multiple-column> IN <subquery>


Is there a way (without JOIN) to use the WHERE clause on 2 columns (OR) IN a subquery? Currently, I'm doing

WHERE 'col1' IN
(
    SELECT id FROM table
) OR 'col2' IN
(
    SELECT id FROM table
)

And I'm sure I can do better :) . i've also tried WHERE ('col1', 'col2') IN <subquery> but MySQL says: Operand should contain 2 column(s)

Thanks for your help.

Edit: By "No join", I mean I'm alreeady making many joins: http://pastebin.com/bRfD21W9, and as you can see, the subqueries are on another table.


Solution

  • I Read you are not agree with JOIN, but just another way to do it.. See join with friends if it is useful to you..

    SELECT `timeline`.`action`, `timeline`.`data`, `timeline`.`tlupdate`,
                u1.`id` AS ufrom_id, u1.`username` AS ufrom_username, u1.`firstname` AS ufrom_firstname, u1.`lastname` AS ufrom_lastname, u1.`picture` AS ufrom_picture,
                u2.`id` AS uto_id, u2.`username` AS uto_username, u2.`firstname` AS uto_firstname, u2.`lastname` AS uto_lastname, u2.`picture` AS uto_picture,
                m.`id` AS m_id, m.`name` AS m_name, m.`alternative_name` AS m_altname, m.`tiny_img` AS m_tiny, m.`normal_img` AS m_normal
        FROM `timeline`
        JOIN `users` u1 ON u1.`id` = `timeline`.`user_id_from`
        JOIN `users` u2 ON u2.`id` = `timeline`.`user_id_to`
        JOIN `friends` f on f.`idol_id`=u1.`id` or f.`idol_id`=u2.`id`
        JOIN `movies` m ON m.`id` = `timeline`.`movie_id`;
    

    Update:

    As you are using inner join You can this too to avoid the condition on complete resultSet.

    JOIN `friends` f on ((f.`idol_id`=u1.`id` or f.`idol_id`=u2.`id`) and f.idol_id = ?)
    

    Either you can use DISTINCT or use GROUP BY to get unique result.