2

Is there a way to write a parametric SQL query that matches notes linked to multiple projects without using HAVING ?

SQLfiddle

Say I have a "notes" table, a "projects" table and a "notes_projects" table linking these two together. I want to find the notes linked to all the project ids in the IN clause. Here is a version with HAVING:

SELECT notes.*, COUNT(np.project_id) AS np_count
FROM notes
INNER JOIN notes_projects AS np
  ON np.note_id = notes.id
WHERE np.project_id IN (?)
GROUP BY notes.id
HAVING np_count = 2;

Is there a way to not use the HAVING clause or is this the correct way to handle such cases ?

Anna B
  • 5,997
  • 5
  • 40
  • 52
  • 1
    See [Divided We Stand: The SQL of Relational Division](https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/) for some other methods. – Martin Smith Aug 16 '13 at 10:59

1 Answers1

0

Don't join the link table because that changes the row count. Formulate the query using EXISTS:

SELECT *
FROM notes n
WHERE EXISTS (
 SELECT *
 FROM notes_projects AS np
 WHERE np.note_id = notes.id AND np.project_id IN (?)
)

(The grouping in your query was just there to fix up the problems the join caused.)

Here is a version that requires all projects to be found:

SELECT *
FROM notes n
WHERE NOT EXISTS (
 SELECT *
 FROM notes_projects AS np
 WHERE np.note_id = notes.id AND np.project_id NOT IN (?)
)
usr
  • 168,620
  • 35
  • 240
  • 369
  • This solution does not work because it finds the notes related to *any* of the projects, not *all*. – Anna B Aug 16 '13 at 10:25
  • Added the requested functionality. Hope I understood the requirement this time. If not, try playing with the code a bit. I'm quite sure you can adapt it to your needs. – usr Aug 16 '13 at 10:53