CODEWITHSUNDEEP
phpmysqlsql
Marcel
Marcel

Reputation: 5119

Find Duplicate in many to many relation

I have a MySQL database table which maps a many to many relation. The database table contains two columns, which are foreign keys from other tables. So this database table only exists for assigning the foreign key from table A to the foreign key from table B.

The structure of the table

column 1: conditionID (int11) (foreign key) column 2: packageID (int11) (foreign key) 

CREATE TABLE `many_to_many_table` (
    `conditionID` int(11) NOT NULL,
    `packageID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `many_to_many_table`
ADD KEY `conditionID` (`conditionID`,`packageID`);

I want to find out, if a combination in this table exists. For example a user has the conditions with the IDs 1, 2 and 3. With these IDs a package with the ID 1 already exists. The entries are 1 | 1, 1 | 2 and 1 | 3.

Sample data

INSERT INTO `many_to_many_table` (`conditionID`, `packageID`) VALUES
(1, 195), (2, 195), (3, 195), (4, 197), (5, 197), (5, 209), (6, 198), (6, 211), (6, 219), (6, 220);

With this sample data the query should return the packageID 195.

I tried the following:

SELECT
    packageID,
    conditionID,
    (
        SELECT 
            COUNT(*)
        FROM
            many_to_many_table
        WHERE 
            conditionID IN (1,2,3)
    ) AS rowCount
FROM
    many_to_many_table
GROUP BY
    packageID
HAVING
    conditionID IN (1,2,3)
AND
    rowCount = 3

This worked fine in the first moment. But it selects entries which have only the conditionID 1 or conditionID 2 and a different packageID. This query is not exact enough to get duplicates.

Is this possible with one query?

Upvotes: 2

Views: 161

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

If I understand you correctly you want:

SELECT packageID
FROM    many_to_many_table
WHERE conditionId IN (1,2,3)
GROUP BY    packageID
HAVING SUM(conditionId = 1) > 0
  AND SUM(conditionId = 2) > 0
  AND SUM(conditionId = 3) > 0;

DBFiddle

Upvotes: 3

Related Questions