How to intersect the table and array in SQL

Question

I have a table named Item. Every item can have 1 to N tags.
Tags are stored in another table named Tag. So, I have transitive dependency.

ID | TagID | ItemID
---|-------|-------
1  |1      |1
2  |2      |1
3  |4      |1
4  |2      |2
5  |3      |2
...

So, I have another table named Block. As table Item, it also has tags, but the relationships between Block and Tag stored in another table.

Each tag has a name, and what is most important, I can have two tags, with the same name, but they related to different tables (one to Block, and another to Item).

Example of Tag table:
ID | Name  | CreatedAt
---|-------|------------
1  | Good  | <Some Date>
2  | Good  | <Some Date>
3  | Bad   | <Some Date>
4  | Worst | <Some Date>
5  | Worst | <Some Date>

Block has tags "Good" with ID 1, "Worst" with ID 4.
Item has tags "Good" with ID 2, "Worst" with ID 5.

What I want to do: I am collecting tags, which are related to particular Block, then I am looking for tags with the same name, which are related to Items. So, If I am collecting tags with IDs 1 and 4, as the output I receive tags 2 and 5.

The most interesting part: I want to find Items which has all required tags. So, I want to find Items which has tags 2 and 5 ('AND' logic).

P.S. This is what I have tried, but I do not know how to improve it:

SELECT bt.TagID
FROM BlockTag as bt
WHERE bt.BlockID = 1114

INTERSECT

SELECT DISTINCT t.ID
FROM Tag as t, BlockTag as nt
WHERE nt.TagID = t.ID AND t.Name IN (
  SELECT t.Name
  FROM ItemTag as it, Tag as t
  WHERE it.TagID = t.ID AND it.ItemID = 2
)

I tried INTERESCT, IN, and JOIN LEFT - nothing helped me. How can I do this Array intersection?

MPost · Accepted Answer · 2019-08-18 02:45:19Z

1

I don't have a way to fully reproduce your situation, but I think the following will work:

SELECT i.ItemId, COUNT(*)
FROM BlockTag AS bt
  INNER JOIN Tag AS t ON t.[ID] = bt.TagID
  INNER JOIN TagName AS tn ON tn.[Name] = t.[Name]
  INNER JOIN Item AS i ON i.TagId = tn.TagId
GROUP BY i.ItemId
HAVING COUNT(*) = (SELECT COUNT(*) FROM Item AS i2 WHERE i2.ItemId = i.ItemId)

answered Aug 18, 2019 at 2:45

MPost

5352 silver badges7 bronze badges

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

Collectives™ on Stack Overflow

How to intersect the table and array in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related