0

I'm not entirely sure how possible this is in a select statement, or if I'm better getting all results and doing checks myself in Android Studio.

I've got 3 tables, a table that stores Recordings, a Table that stores Tags and a table that links the Tags to the Recordings - TagsLink.

The TagsLink table has 2 columns, one that stores the TagsID and one that stores the RecordingsID

What I'm hoping to do is only return RecordingsIDs that meet the selected Tags criteria. So if TagsID 3 is selected, Recordings 1, 2 and 4 are returned. And if TagsID 3 and 4 are selected, it returns only Recordings 2 and 4.

In my mind it's something along the lines of:

SELECT DISTINCT RecordingsID FROM TagsLink WHERE ...

If this isn't entirely possible, any advice on other ways of achieving this (even if it requires restructuring the database) would be greatly appreciated!

Phantômaxx
  • 37,901
  • 21
  • 84
  • 115
Paul Stringer
  • 90
  • 2
  • 10

2 Answers2

2

With this kind of query:

SELECT
RecordingsID
FROM
TagsLink
WHERE
TagsID IN (3, 4, ...)
GROUP BY
RecordingsID
HAVING COUNT(*) = 2 -- This number must match the number of tag IDs specified in the IN (...) list.

The key is to remember to adjust the count based on the tags you want to filter on.

Other similar and helpful answers here and here and here.

EDIT

To accommodate additional tables, filtering on different columns, use INTERSECT as follows:

SELECT
RecordingsID
FROM
TagsLink
WHERE
TagsID IN (3, 4, ...)
GROUP BY
RecordingsID
HAVING COUNT(*) = 2 -- This number must match the number of tag IDs specified in the IN (...) list.

INTERSECT

SELECT
RecordingsID
FROM
ContactsLink
WHERE
ContactsID IN (100, ...)
GROUP BY
RecordingsID
HAVING COUNT(*) = 1 -- This number must match the number of contacts IDs specified in the IN (...) list.
Andy
  • 1,307
  • 1
  • 12
  • 17
  • Thank you so much, you're a legend!!! Thanks for the other links too, I tried looking but my results weren't bringing much back! – Paul Stringer Aug 01 '17 at 14:05
  • @PaulStringer pleasure! Be sure to accept it as the right answer if it works for you :) – Andy Aug 01 '17 at 14:07
  • Been racking my brain trying to figure out how I'd do this for multiple tables, don't suppose you'd know how I'd go about it? Basically, you can link Contacts and Tags to a Recording, so I've also got the TagsLink table and also a ContactsLink table. So if I selected 2 tags and 1 contact, I'd want all recording Id's that have both Tags and the Contact against them?! Would I be best just concatenating the 2 tables to one link table with a reference column? – Paul Stringer Aug 01 '17 at 19:45
1

This should work: SELECT RecordingsID FROM tagslink WHERE TagsID = 4 Intersect SELECT RecordingsID FROM tagslink WHERE TagsID = 3 I could not test it with sqlite. However, the function to use is Intersect, not using parentheses that sqlite does not support them

Luca
  • 132
  • 3