Query to select all elements not having a certain field

Question

First of all I'm using mysql 5.6

I want to select all the elements that satisfy at least one of these request:

Do not have a certain field.
A certain field has an empty value (for empty I mean: len(trim(value)) == 0)

I'll make an example to be more clear

table2
id|field|value
1 |x    |12
1 |y    |23
1 |z    |34
2 |x    |45
2 |y    |56
2 |z    |  <---- is an empty string with 0/1 spaces
3 |x    |67

So what I want is for example if I want all field that do not have field z I should obtain (2,3) In case of all fields without y I should obtain (3)

This is what I tried without much success:

SELECT t12.id 
FROM table2 AS t12
WHERE NOT IN ( 
    SELECT t2.id 
    FROM table2 as t2
    WHERE t2.field = 'y' 
        AND t2.value <> '' AND t2.value IS NOT NULL
)

Removed typo! Thank you, the original table structure is much more complex — Timmy, Apr 21 '18 at 16:07
`t1.field` should be `t2.field`. Why do you need to join with `table1`? You're not using it for anything. — Barmar, Apr 21 '18 at 16:08
Put parentheses around the `OR` part of the query. See https://stackoverflow.com/questions/27663976/sql-statement-is-ignoring-where-parameter — Barmar, Apr 21 '18 at 16:11
Why are you checking `t2.value`? There's nothing in the problem description that mentions that column. — Barmar, Apr 21 '18 at 16:14
Typo: `WHERE NOT IN` should be `WHERE t12.id NOT IN`. The query works with that fix. — Barmar, Apr 21 '18 at 16:18
I've edited it here: http://www.sqlfiddle.com/#!9/29e55f/1 Seems to be working! Thank you! — Timmy, Apr 21 '18 at 16:23
And if you want to get rid of duplicates, use `SELECT DISTINCT`. — Barmar, Apr 21 '18 at 16:27
@Barmar Thank you, if you answer I'll give you the the best answer for the help :) — Timmy, Apr 24 '18 at 06:20

shawnt00 · Answer 1 · 2018-04-21T16:33:22.500

0

select id from t group by id
having min(len(value)) = 0 or count(case when field = 'x' then 1 end) = 0

Combine multiple conditions in the case if the criteria must be met by a single row rather than the whole group.

edited Apr 21 '18 at 16:33

answered Apr 21 '18 at 16:28

shawnt00

16,443
3
17
22

Query to select all elements not having a certain field

1 Answers1