-1

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

  • id
  • lesson_id
  • tag_id.

relation picure

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

I have tried some sql queries 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 expected answer.

philipxy
  • 14,867
  • 6
  • 39
  • 83
aviateur22
  • 45
  • 1
  • 8
  • 1
    Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. Please in code questions give a [mre]. [ask] [Help] – philipxy Mar 30 '22 at 00:35

1 Answers1

1

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

Salman A
  • 262,204
  • 82
  • 430
  • 521
  • i don't understand the logic of you query :). But it's seem to do the job perfectly. – aviateur22 Mar 29 '22 at 15:27
  • See https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/divided-we-stand-the-sql-of-relational-division/ for full explanation (search for "not exists") + other methods. – Salman A Mar 29 '22 at 17:01