CODEWITHSUNDEEP
mysql
magnol
magnol

Reputation: 342

Retrieve records with multple matches in link table

I'm struggling with SQL and would value some assistance.

I have a main table and a one-to-many link table in mySQL, joined by oppreg_id. I want to retrieve all the records on the main table that have multiple matches to the field activity_id in the link table.

main table            link table
oppreg                oppreg2activity
---------------       -----------------
oppreg_id   ---->     oppreg_id
title                 activity_id
description
end_date
...

So I might have 

oppreg_id = 4563 in the main table

and records in the oppreg2activity table

oppreg_id  activity_ID
  4563      27e17f2e
  4563      38d6bd73
  4563      ....

I've tried SQL like

SELECT oppreg.`oppreg_id`,`title`,`description`, activity_id 
FROM `oppreg` 
INNER JOIN oppreg2activity ON oppreg.oppreg_id = oppreg2activity.oppreg_id
GROUP BY `oppreg_id`
HAVING 
  activity_id = '27e17f2e' 
  AND activity_id = '38d6bd73'

This works when one WHERE item is present but fails on multiple ones.

I also need to include the additional restriction

oppreg.end_date >= CURDATE()

but can't see how to include this.

Upvotes: 0

Views: 30

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521419

Your suggestion to aggregate by oppreg_id is correct, but you should but asserting counts in the HAVING clause, instead of using plain equalities.

SELECT
    o.oppreg_id
FROM oppreg o
INNER JOIN oppreg2activity oa
    ON o.oppreg_id = oa.oppreg_id
WHERE
    oa.activity_id IN ('27e17f2e', '38d6bd73') AND
    o.end_date >= CURDATE()
GROUP BY
    o.oppreg_id
HAVING
    COUNT(DISTINCT activity_id) = 2;

Note that it only makes sense to select the title and description if the oppreg_id column be a primary key in the oppreg table. If not, then we would have to do an additional join to bring in this extra information.

As for an explanation of the query, it works by attempting to reduce down each oppreg_id group to only those records having the two specified activity_id values. It then aggregates and asserts using COUNT that two distinct activity_id are present, which, if true, implies that this oppreg_id group is a match.

Upvotes: 3

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

i think you need where for filter date and count for multipe entry checking

SELECT oppreg.`oppreg_id`,
`title`,`description`, activity_id 
FROM `oppreg` 
INNER JOIN oppreg2activity ON oppreg.oppreg_id = oppreg2activity.oppreg_id
where oppreg.end_date >= CURDATE()
GROUP BY oppreg.`oppreg_id`,
`title`,`description`, activity_id
having count(*)>1

Upvotes: 1

Related Questions