CODEWITHSUNDEEP
phpmysqlsqlinner-joinsemi-join
nielsv
nielsv

Reputation: 6810

MYSQL Query double JOIN

This is my query:

SELECT count(*) AS COUNT
FROM `psttodo-in` p
INNER JOIN `deelgebied` d 
    ON d.`Segmentcode` = p.`Segment No_ PST`
    AND d.`Deelgebied` = p.`Deelgebied`
INNER JOIN m2m 
    ON m2m.`deelgebied` = d.`deelgebiedID`
WHERE 
    p.`Segment No_ PST` = 'PSS1400146'
    AND p.`Deelgebied` = 2
ORDER BY `afgewerkt tablet datum`

Now when I check my table psttodo-in in Sequal Pro and I select the rows where Segment No_ PST = PSS1400146 and Deelgebied = 2 I count 84. But when I run the query I get a result of 252. What am I doing wrong?

UPDATE:

My table structure :

table psttodo-in:
    PK No_
    Hostess Code
    Segment No_
    FK Deelgebied
    ....

table deelgebied
    Segmentcode
    Deelgebied
    map
    DeelgebiedID
    pst-active

table m2m
    PK m2mID
    FK deelgebied
    FK psthostess

Upvotes: 1

Views: 271

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562388

There's always exactly one row in d for each row in p, because of the foreign key reference.

But there may be multiple rows in m2m for each or in d. In fact, since 252 is 84 * 3, I would guess that there are three rows in m2m for each d (or at least the average is three). Thus in the joined result set, the rows are tripled, with distinct rows from m2m but the rows from p and d are repeated.

There are a couple of ways to fix this:

Count each p only once

We know that in the repeated rows, the values from p and d are repeated. So pick a column that is known to be unique in either p or d, and count only the distinct values in it. We can't pick d because those might be chosen more than once legitimately, if two different p entries reference the same d. So pick the primary key of p:

SELECT COUNT(DISTINCT p.NO_) AS COUNT
FROM `psttodo-in` p
INNER JOIN `deelgebied` d 
    ON d.`Segmentcode` = p.`Segment No_ PST`
    AND d.`Deelgebied` = p.`Deelgebied`
INNER JOIN m2m 
    ON m2m.`deelgebied` = d.`deelgebiedID`
WHERE 
    p.`Segment No_ PST` = 'PSS1400146'
    AND p.`Deelgebied` = 2
ORDER BY `afgewerkt tablet datum`

Use a semi-join

A semi-join returns only one row of the result even if there are multiple matches. The way to write a semi-join in SQL is like this:

SELECT COUNT(*) AS COUNT
FROM `psttodo-in` p
INNER JOIN `deelgebied` d 
    ON d.`Segmentcode` = p.`Segment No_ PST`
    AND d.`Deelgebied` = p.`Deelgebied`
WHERE 
    p.`Segment No_ PST` = 'PSS1400146'
    AND p.`Deelgebied` = 2
    AND EXISTS (SELECT * FROM m2m WHERE m2m.`deelgebied` = d.`deelgebiedID`)
ORDER BY `afgewerkt tablet datum`

Semi-join optimization was improved in MySQL 5.6, so I recommend upgrading if you use this solution.

Upvotes: 1

Related Questions