MySQL WHERE <multiple-column> IN <subquery>

Question

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.

Diez

why not join? any specific reason? – Guthrun

This is because I'm already joining a lot. I've updated my original post to add an extract of my current query. – Diez

there are just 3 joins, i don't say there is a lot join.. i suggest you to make one more join instead of making inner query. – Guthrun

Can you suggest me something? I don't see how to add it, as there is a notion of "OR" instead of "=" :/ – Diez

i had eyes on your question, even i was also looking for another way if someone can suggest but.. not found yet. – Guthrun

you've already suggested something on your post below – Diez

let us continue this discussion in chat – Diez

Answer 1

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.

Guthrun

Closer to what I need. Actually, I'm not so close minded (^^), I wanted to know if it was possible without JOIN. I'm not an SQL master, so thank you for your help. I've added your JOIN and a WHERE idol_id = ? to filter them, but I have many times the same rows (12 times, exact same answer) :/ Do you know how to avoid DUPLICATE? (I know the keyword DISTINCT, but I think it would be a hack if I use it) – Diez

The data I want is f.idol_id WHERE f.fan_id = ?, so I think rewriting f.idol_id = ? to f.fan_id = ? will be enough ? – Diez

Answer 2

SELECT *
FROM table

WHERE 
(col_1, col_2) NOT IN
 (SELECT col_1, col_2 FROM table)

Santosantonica

Answer 3

Rewrite your code like this and you're good to go:

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

Hankering

It's closer than what I need. But ('col1', 'col2') means "AND", and what I need is "OR" ^^' – Diez

Answer 4

manurajhada answer is good. If you still decide to avoid the JOIN you could:

SELECT ... FROM (SELECT id FROM table) subquery, other_table
WHERE other_table.col1 = subquery.id OR other_table.col2 = subquery.id

This should make MySQL use the join buffer to hold the results of the subquery.

Headdress

Answer 5

select * from table1  WHERE (col1, col2) in (select subquerycol1, subquerycol2 from subquery_table where some_condition=1)

Bernardo

Answer 6

You can try below query:

select name from user_details where groupid IN ( select g_id from user_groups ) OR  cityid IN ( select c_id from user_cities );  

Hope this help.

Gunwale

It could help, but the subqueries are the same. And I need something simplier than your query, which is my current query. – Diez

Answer 7

I also face similar problem

SELECT COUNT(*) FROM msg_old WHERE idfrom IN 
    (SELECT olduserid FROM temp_user)
    OR
    idto IN (SELECT olduserid FROM temp_user)

I came up with solution

SELECT * FROM msg_old 
INNER JOIN temp_user 
ON msg_old.IDFROM = temp_user.olduserid
OR msg_old.IDTO = temp_user.olduserid 

Trierarch

The use of OR may potentially slowdown the query. See #11702794. – Bluff

Answer 8

yes, you can do. It is easy enough. you must select same number of columns in sub query as you mentioned in the where clause.

Firearm

any example would help better !! – Guthrun

I think, he means @martin-kh query (below) – Diez