Multi value field sql query, check if value exist

Question

I do have a problem with a query. I spend hours trying to find a way to make it work but I can't :( Even if it feels easy !

Let's say I have this table:

entity_id    delta    name
----------------------------
1            0        speaker
1            1        screen
2            0        mouse
2            1        TV
2            2        tablet

I simply want to return the entity id of a content where name != 'speaker' (in this example, it would be entity_id = 2).

If I use "name LIKE 'speaker'", it returns me entity 1. But I want the other way. So I try to use "name NOT LIKE 'test'", it returns me all the other rows, which is normal, but I would like to only get entity_id 2.

I feel like I am missing something easy.

Thanks for you help !

Ed

I'm find difficult to understand, can you please formulate an example? — Gianmarco
– Gianmarco, Commented Dec 14, 2015 at 0:18
I tried to correct a few sentences and use different names. Is it better? — Doud
– Doud, Commented Dec 14, 2015 at 0:25
but in your second row, "screen" has entity_id "1". So, that's NOT "speaker", but still "1" (???) — Johannes
– Johannes, Commented Dec 14, 2015 at 0:25
See answer from @juergen d, it is a multi field value so multiple rows can be linked to the same content. Sorry I wasn't clear enough. Thanks! — Doud
– Doud, Commented Dec 14, 2015 at 0:30

juergen d · Accepted Answer · 2015-12-14 00:21:57Z

1

select entity_id
from your_table
group by entity_id
having sum(name = 'test') = 0

answered Dec 14, 2015 at 0:21

juergen d

205k40 gold badges304 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Doud Over a year ago

Works great ! Thanks a lot :)

Collectives™ on Stack Overflow

Multi value field sql query, check if value exist

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related