MySQL - Conditions with precedence

Question

I have a table called terms with id, name, school_id and klass_id as its attributes. I want to return records that satisfies one of the condition. Before I explain the problem in detail, take a look at the table output:

id      name    school_id      klass_id
---------------------------------------
1       Term1   NULL           NULL
2       Term2   NULL           NULL
3       Term1   1              4
4       Term2   1              4
5       Term1   1              3
6       Term2   1              3
7       Term1   1              NULL
8       Term2   1              NULL
9       Term1   7              NULL
10      Term2   7              NULL
12      Term1   7              103
13      Term2   7              103
14      term3   7              103
15      Term1   7              30
16      Term2   7              30
17      Term1   7              32
18      Term2   7              32

Now, notice that there are three kinds of scenarios here between klass_id and school_id

Both klass_id and school_id is not NULL (condition 1)
Only klass_id is NULL but not school_id (condition 2)
Both school_id and klass_id is NULL (condition 3)

I'd like to return only those records that satisfies the first condition; if it doesn't, return only those records that satisfies the second condition; if it doesn't return only those records that satisfies the third condition. How do I accomplish this in simple database query?

What have you tried? What worked or didn't work, and in which ways? (Not only does this show "effort", but it can also be used to showcase correct/incorrect results or specific problems.) — , Dec 27 '11 at 06:19
[I belive its answered][1] [1]: http://stackoverflow.com/questions/8601148/complex-codeignitor-user-search-query/8607503#8607503 — Philip, Dec 27 '11 at 06:22
I've tried using pipe operator. I've heard this somewhere that in computer science, a single pipe('|') operator evaluates an expression that happens to be true, ignoring the rest in the chain. Something like this `SELECT * FROM terms WHERE (klass_id = 25 && klass_id IS NOT NULL) | (school_id = 2 && klass_id IS NULL) | (school_id IS NULL && klass_id IS NULL)`. It works fine for the first 2 conditions, but if the first two conditions are false, for some reason, the 3rd condition just doesn't work — Vineeth Pradhan, Dec 27 '11 at 06:26
Hi @Tudor thanks for your response, much apprechiated. To backup my point I do think its related as my answer shows how to answer complex join questions, ok it may not have been specific but I think generally it relates to the architecture.. — Philip, Dec 27 '11 at 06:40
@Philip - then it is related to absolutely any SO question that involves `JOIN`s — Tudor Constantin, Dec 27 '11 at 06:43
generalisation yes! if you look at his OL of questions, I think my answer complemets it. — Philip, Dec 27 '11 at 06:52

Tudor Constantin · Accepted Answer · 2011-12-27T06:38:15.260

3

Something like this should do the trick:

    SELECT *, ( SELECT MIN(condition_number) as first_condition_met FROM (
 ( SELECT t.*, 1 as condition_number FROM terms t WHERE ... ) #first condition in WHERE clause
         UNION
       ( SELECT t.*, 2 as condition_number FROM terms t WHERE ... ) #second condition in WHERE clause
         UNION
       ( SELECT t.*, 3 as condition_number FROM terms t WHERE ... ) #third condition in WHERE clause
) ) FROM (
       ( SELECT t.*, 1 as condition_number FROM terms t WHERE ... ) #first condition in WHERE clause
         UNION
       ( SELECT t.*, 2 as condition_number FROM terms t WHERE ... ) #second condition in WHERE clause
         UNION
       ( SELECT t.*, 3 as condition_number FROM terms t WHERE ... ) #third condition in WHERE clause
    ) WHERE condition_number = first_condition_number

edited Dec 27 '11 at 06:38

answered Dec 27 '11 at 06:28

Tudor Constantin

26,330
7
49
72

Thanks, I tried your solution but I get the error `#1248 - Every derived table must have its own alias` – Vineeth Pradhan Dec 27 '11 at 06:50
My query is just a starting point - syntax errors and concrete `where clauses` still have to be solved/implemented - see [this SO question][1] for the error you are getting [1][http://stackoverflow.com/questions/1888779/every-derived-table-must-have-its-own-alias] – Tudor Constantin Dec 27 '11 at 07:10
1

@TudorConstantin: Tagged links do not work in comments. You can use this template instead: `[your text](your url)` (works in ‘normal’ posts too). – Andriy M Dec 27 '11 at 07:22

score 0 · Answer 2 · answered Dec 27 '11 at 11:03

SELECT *
FROM 
    terms
  CROSS JOIN
    ( SELECT CASE WHEN EXISTS 
                       ( SELECT *
                         FROM terms
                         WHERE school_id IS NOT NULL
                           AND klass_id IS NOT NULL
                       )
                      THEN 1
                  WHEN EXISTS 
                       ( SELECT *
                         FROM terms
                         WHERE school_id IS NOT NULL
                       )  
                      THEN 2
                  ELSE 3
             END AS result
    ) AS test
WHERE (test.result = 1 
      AND school_id IS NOT NULL
      AND klass_id IS NOT NULL
      )
   OR (test.result = 2 
      AND school_id IS NOT NULL
      AND klass_id IS NULL
      )
   OR (test.result = 3
      AND school_id IS NULL
      AND klass_id IS NULL
      )

MySQL - Conditions with precedence

2 Answers2