Question 192524 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – how to use the "IN" of SQL to check if a value equals to ALL columns in the parenthesis

I have an sql table in phpmyadmin db which has 8 columns. I want to check if 4 of them (specific 4 columns) equals to some value.
For the example, lets say the value is "abcd", and the columns names are: c1, c2, c5, c8.

I tried to do this query:

SELECT * FROM table_name t WHERE 'abcd' IN (t.c1, t.c2, t.c5, t.c8)

The problem is that even if ONE of the 4 columns is "abcd", I get a row result, but I want the condition to be true only if ALL the 4 columns are "abcd".

I tried also to read about ANY and ALL but I didn’t think it would work.

Of course I can write the WHERE clause as t.c1 = 'abcd' AND t.c2 = 'abcd' and so on… But I want a short way (if possible) without writing the value "abcd" many times.

How can I write this query?

Tags:

2

Answers


  1. 0

    How about

    where concat(c1,c2,c5,c8) = 'abcdabcdabcdabcd'

    You may need to use + or || depending on your specific database.

    Depending on your data you might need to also delimit, eg

    where concat(c1,'-',c2,'-',c5,'-',c8) = 'abcd-abcd-abcd-abcd'
    Login or Signup to reply.
  2. 0

    If you want to focus only on the four columns (c1, c2, c5, and c8) being "abcd" and exclude any other columns you need to use the IN operator to compare the tuple (t.c1, t.c2, t.c5, t.c8) with the tuple (‘abcd’, ‘abcd’, ‘abcd’, ‘abcd’). If all four values in the columns match the respective values in the tuple, the row will be returned.

    CREATE TABLE MyTable (
  id INT PRIMARY KEY AUTO_INCREMENT,
  c1 VARCHAR(50),
  c2 VARCHAR(50),
  c3 VARCHAR(50),
  c4 VARCHAR(50),
  c5 VARCHAR(50),
  c6 VARCHAR(50),
  c7 VARCHAR(50),
  c8 VARCHAR(50)
);

INSERT INTO MyTable (c1, c2, c3, c4, c5, c6, c7, c8)
VALUES
  ('abcd', 'efgh', 'ijkl', 'mnop', 'abcd', 'qrst', 'uvwx', 'abcd'),
  ('abcd', 'abcd', 'ijkl', 'efrg', 'abcd', 'efgh', 'efgh', 'abcd'),
  ('efgh', 'ijkl', 'mnop', 'qrst', 'uvwx', 'abcd', 'efgh', 'ijkl'),
  ('abcd', 'efgh', 'ijkl', 'mnop', 'qrst', 'uvwx', 'efgh', 'ijkl');


    Records: 4  Duplicates: 0  Warnings: 0

    SELECT *
FROM MyTable t
WHERE ('abcd', 'abcd', 'abcd', 'abcd') IN ((t.c1, t.c2, t.c5, t.c8));
    id c1 c2 c3 c4 c5 c6 c7 c8
    2 abcd abcd ijkl efrg abcd efgh efgh abcd

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search