I a m working with nodejs and postgresql. My postgresql relation has 3 columns:

• id
• lesson_id
• tag_id.

A lesson could belong to 1 or multiple tags

I am trying to select all the lesson whose belongs to the requested tags.

For example

• tags requested are id 10 and 2, the query should response with lesson id = 3
• tags requested are id 1 and 17, the query should response with lesson id = 6
• tag requested is 3, the query should response with lessons id 1, 2, 4

if have try some sql query like this one

 const selectLessonByTag = await pgClient.query(            
       `SELECT DISTINCT ON (lesson_id)
       lesson_id FROM "lesson_has_tag"
       WHERE tag_id = $1  AND tag_id = $2
       GROUP BY lesson_id        
       ORDER BY lesson_id`, 
       [2,10]);

but it's not the excpected answer

your help will be very appreciated

thanks a lot

Cyrille

CodePudding user response：

You can use not exists like so:

select distinct lesson_id
from lesson_tags as lt1
where not exists (
    select *
    from (values (10), (2)) as required_tags(tag_id)
    where not exists (
        select *
        from lesson_tags as lt2
        where lt2.lesson_id = lt1.lesson_id and lt2.tag_id = required_tags.tag_id
    )
)

It is difficult to digest so little explanation:

• There is a table valued constructor called required_tags containing values 10 and 2
• The inner query tests if 10 or 2 do not exist for a lesson from the outer query
• If the inner query does not produce a match the outer row selected

DB<>Fiddle