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

Mysql – SQL CASE based on specific values

I am trying to understand SQL better and have run into a problem when it comes to key-value pairs (I think there is a better term for this, but am not sure).

Contract Contract Type Column1 Column2
Contract 1 Type 1 x
Contract 1 Type 2 x
Contract 2 Type 1 x
Contract 2 Type 2 x

I apologize if this example isn’t clear. I know there might be a way to set up this table better, however this is just an example I made up.

I am trying to create an SQL query that checks whether contracts are set up correctly. As an example, Contract 1 is set up correctly, as there is an x in column1 when it says ‘type 1’, and an x in column2 when it says ‘type 2’.

However, I am unsure how to write the CASE statement for Contract 2, as it requires checking each contract individually to see if either row has an ‘x’ in the correct places.

So far I’ve written the CASE statements for when there’s no ‘x’s in either row, and ‘x’s in both:

SELECT DISTINCT Table1.Contract,
   Table1.ContractType,
   Table1.Column1,
   Table1.Column2,
   Table1.Site
   Table1.Date,
   CASE
      WHEN Column1 <> 'x' AND Column2 <> 'x' THEN 'Error'
      WHEN Column1 = 'x' AND Column2 = 'x' THEN 'Error'
   END AS 'Results'
FROM...
JOIN...

WHERE Contract IN (
     SELECT Contract
     FROM Table1 
     WHERE ContractType IN ('Type1','Type2','Type3') AND 
         Site = 'Site 1' AND 
         Contract HAVING COUNT(DISTINCT ContractType) >= 2
) AND Date >= CURRENT_TIMESTAMP
ORDER BY Contract

How do I check per Contract that there is x in Column1 for the first row and check that there is an x in Column2 for the second row?

Thank you!

EDIT: Edited in the ‘full query’, with some abstraction as to not show real data.
Something that I might have poorly explained: Technically, it would be okay if Column2 has an ‘x’ in a Type1 row and vice versa. The important part is that for each Contract, there’s an ‘x’ in both columns, via different rows.

Tags:

2

Answers


  1. 0

    Based on the new requirements (which I believe are as follows

    • Each row must have a value in column1 or column2

    • Contract type can not be repeated

    • there should be 2 rows

    • based on code this may need to be 3 or more columns in the first bullet

    Here is the code to solve:

     SELECT 
    -- ... all the columns for the data you need
    COUNT(Column1 = 'x') as col1cnt,
    COUNT(Column2 = 'x') as col2cnt,
    COUNT(ContactType) as ctrows
 FROM tablenameyoudidnotsay
 GROUP BY Contract
 WHERE col1cnt = 1 and col2cnt = 1 and ctrows = 2

    Note — based on the query above if you wanted the other columns that are not in the group by you would need to make this a subquery joined to the main query on Contract — to only pull the correct ones OR add a not around the where clauses to only pull the bad ones.

    Login or Signup to reply.
  2. 0

    You need to use GROUP BY to define groups of rows, and then a apply CASE clause to check whether contracts are set up correctly or not :

    SELECT Contract, 
       case when count(distinct Contract_Type ) = 2 
                 and max(column1) = 'x' 
                 and max(column2) = 'x'
            then 'OK' 
            else 'NOK' 
       end as results
FROM mytable
WHERE Contract_Type = 'Type 1' OR
      Contract_Type = 'Type 2'
GROUP BY Contract;

    Your table schema is "EAV" or Entity-Attribute-Value. This is a common schema for applications to use if the number of attributes per entity is unknown or volatile

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

Back To Top
Search