is there a sql query for finding if a table consists some rows that have specific value in them where the value is not the same to all rows search

user_16365106 0
Viewed 31

For example I have a table x that has columns (col1,col2,col3) and it has the values

col1 col2 col3
1 2 3
2 4 5
1 2 7
5 8 7

and I want to get true if the table contains for all col1=1 and that both 3 and 7 are good values for col3 meaning to search for every row with col1=1 and col3=3 or 7. I tried doing so with contains but I failed. also is it possible to want that for the value 2 in col1 i will have 3 in col3 and for the value 1 in col1 i will have 7 in col3? thank a lot if someone can help me I will be very thankful. thank you all

mysql sql
3 Answers

You can do it with simple where clause as below:

DB-Fiddle:

 select * from x where col1=1 and col3 in (3,7)

Output:

col1 col2 col3
1 2 3
1 2 7

db<>fiddle here

To calculate an extra column having true or false:

 select col1,col2,col3,
 (case when col1=1 and col3 in (3,7) then 'true' else 'false' end)validate from x

Output:

col1 col2 col3 validate
1 2 3 true
2 4 5 false
1 2 7 true
5 8 7 false

db<>fiddle here

As mentioned in comment:

 select col1,col2,col3,
 (case when (col1=2 and col3 = 3) or (col1=1 and col3 = 7) then 'true' else 'false' end)validate from x

Output:

col1 col2 col3 validate
1 2 3 false
2 4 5 false
1 2 7 true
5 8 7 false

db<>fiddle here

user_191212020

Try this request:

Select * from x where (Col1 = 1 and Col3 =3 ) or (Col1 = 1 and Col3 =7)

user_79243530

You want to select one value, true or false. In your example you will return true, because all rows with col1 = 1 match the condition col3 in (3,7). If there were a row with col1 = 1 and col3 not in (3,7) you would return false.

So let's see whether there are mismatches in the table. If so, the result shall be false otherwise true.

This simple query does this:

select
  not max(col1 = 1 and col3 not in (3,7)) as is_okay
from mytable;

We are selecting the maximum for a boolean expression. In MySQL true = 1, false = 0. So if we find a mismatch then the result is 1 (true), else 0 (false). And with NOT we make it vice versa, i.e. get true when there is no mismatch.

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=229038965ccf8429ec783139063dbdfb

user_22707620
Related