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

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

CodePudding user response：

Try this request:

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

CodePudding user response：

You can do it with simple where clause as below:

DB-Fiddle:

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

Output:

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:

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:

db<>fiddle here

CodePudding user response：

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