CODEWITHSUNDEEP
mysqlsqlruby-on-railsrubypostgresql
Yassine
Yassine

Reputation: 183

SQL : ids containing specific arrays in a specific way

An item table: items has many taxonomies table: taxonomies using a join table item_taxonomies (item_id, taxonomy_id).

A search for items is made using taxonomies groups.

Example:

taxo_group_1 = [1, 2, 3]
taxo_group_2 = [4, 5]

The sql should find all items having taxonomies included in both arrays this way:

If I have these elements:

item_1 id=1
   taxo_1 id=11
   taxo_2 id=12
   taxo_3 id=13

item_2 id=2
   taxo_3 id=13
   taxo_4 id=14

Searching with [11, 12] and [13] will return item_1 and not item_2, because item_1 has taxonomies in [11, 12] AND in [13].

item_2 won't be returned because it has not taxonomies in [11, 12]

So far:

"taxonomies"."id" IN (11, 12, 13) AND "taxonomies"."id" IN (13)

Won't work of course.

Upvotes: 0

Views: 42

Answers (3)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

You can get your desired output using this query :

select item from (
    select item, count(distinct taxonomy_id) as count from items  
    join taxonomies on items.item_id = taxonomies.item_id
    where taxonomies.taxonomy_id in (11,12,13)
    group by item
) as T where count = 3

Upvotes: 1

monostabil
monostabil

Reputation: 11

I have unterstood it a bit differently. He/she wants to find all items that have exactly 11, 12 and 13 as taxonomy id.

select item from joinedTable 
where taxonomy_id in (11,12,13)
group by item
having count(distinct taxonomy_id) = 3

But really I am a bit confused about the request.

Upvotes: 1

Daniel E.
Daniel E.

Reputation: 2480

If I understand well your question you want to get a group who gets all id in entry, in sql it will be :

SELECT [...] WHERE taxonomies.id = 11 
   AND taxonomies.id = 12 
   AND taxonomies.id = 13

Upvotes: 0

Related Questions