CODEWITHSUNDEEP
sqlsqlite
phramusca
phramusca

Reputation: 123

sqlite 1 to many schema: query rows linked to itemX and itemY only

I have a database with a one to many relationship. 

CREATE TABLE 'tag' (
    'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    'value' TEXT NOT NULL,
    CONSTRAINT name_unique UNIQUE ('value')
);

CREATE TABLE 'tagfile' (
    'idFile' INTEGER NOT NULL,
    'idTag' INTEGER NOT NULL,
    PRIMARY KEY ('idFile', 'idTag'),
    FOREIGN KEY(idFile) REFERENCES tracks(ID),
    FOREIGN KEY(idTag) REFERENCES tag(id) ON DELETE CASCADE
);

How can I query it to get list of idFile:

Upvotes: 0

Views: 26

Answers (2)

phramusca
phramusca

Reputation: 123

Ended up using this solution though it requires tags not to overlap (or some weird results may happen but not a big deal in my case). If anyone has a better query though, I'd be glad to hear from :)

select tf.idFile, GROUP_CONCAT(t.value) AS tags
from tagfile tf join
     tag t
     on tf.idtag = t.id
group by tf.idFile
having tags LIKE "%a%"
AND tags LIKE "%b%"
AND tags LIKE "%c%"
AND tags NOT LIKE "%d%" -- to filter out

Edit: Finally using solution from Gordon. Here is another version:

select tf.idFile, GROUP_CONCAT(t.value) AS tags
from tagfile tf join
     tag t
     on tf.idtag = t.id
group by tf.idFile
-- Those to include: ( = length of include list)
having sum(case when t.value in ('a', 'b', 'c') then 1 else 0 end) = 3  
-- Those to exclude: ( = 0)
and sum(case when t.value in ('z', 'y') then 1 else 0 end) = 0          
 -- for exact list match, add:
 -- and count(*) = 3 -- ( = length of include list)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can use group by and having:

select tf.idFile
from tagfile tf join
     tag t
     on tf.idtag = t.id
where t.value in ('a', 'b', 'c')
group by tf.idFile
having count(*) = 3;  -- length of list

If you want files that have those three and others, then use the above. If you want exactly those three:

select tf.idFile
from tagfile tf join
     tag t
     on tf.idtag = t.id
group by tf.idFile
having sum(case when t.value in ('a', 'b', 'c') then 1 else 0 end) = 3 and
       count(*) = 3;  -- length of list

Upvotes: 1

Related Questions