CODEWITHSUNDEEP
mysqlsqlsql-server
Georgi Angelov
Georgi Angelov

Reputation: 4388

SQL - Select multiple columns with WHERE IN clause

So I am trying to use a WHERE IN clause where I use multiple columns instead of a single column.

This is what I tried but I keep getting an error:

"error near "," ... "

Statement:

SELECT 
    *
FROM
    language
WHERE
    (char1 , char2, char3, char4) in (('H' , 'H', 'B', 'B') , ('B' , 'B', 'B', 'M'))
        AND language.type = 'M'

I am not sure what the error here is. I was wondering if anyone has tried something similar. It seems like a trivial query. Thank you.

Upvotes: 1

Views: 165

Answers (2)

Bridge
Bridge

Reputation: 30651

This is invalid syntax. What are you trying to do? See where all four of those columns match exactly the values in each set of brackets?

If so, you need to specify like this:

WHERE language.type = 'M' AND
 ((char1 = 'H' AND char2 = 'H' AND char3 = 'B' AND char4 = 'B') OR
 (char1 = 'B' AND char2 = 'B' AND char3 = 'B' AND char4 = 'M'))

Upvotes: 3

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

You can't do that with an IN clause (at least not that way).

One way would be to use or clauses

((char1 = 'H' and char2 = 'H' and char3 = 'B' and char4 = 'B') or
(char1 = 'B' and char2 = 'B' and char3 = 'B' and char4 = 'M'))

and language.type = 'M'

if you really wanna use an IN clause, you may use concatenation

something like this (which will work only with this sample, so you may need to adapt by adding a separator between fields and searched values)

where
 concat(char1, char2, char3, char4) IN ('HHBB', 'BBBM')

Upvotes: 7

Related Questions