CODEWITHSUNDEEP
mysqlsql
jskidd3
jskidd3

Reputation: 4783

MySQL join table twice without duplicating data

SELECT SEC_TO_TIME(SUM(l1.elapsed)) AS run
FROM pattern
INNER JOIN link l1 ON l1.section = pattern.section
WHERE pattern.service = "44-A-B-y10-1" AND pattern.direction = 'outbound'

The above query returns 00:30:00 for the run value in the select statement. I now need to add a second reference to the link table. I won't bother going into why as it's not directly related to the question.

SELECT SEC_TO_TIME(SUM(l1.elapsed)) AS run
FROM pattern
INNER JOIN link l1 ON l1.section = pattern.section
INNER JOIN link l2 ON l2.section = pattern.section
WHERE pattern.service = "44-A-B-y10-1" AND pattern.direction = 'outbound'

Above is the new query. The problem is, I would have expected run to still return 00:30:00 as it is only getting the SUM of l1, but it now returns 32:00:00. Could someone please explain why this is happening? I need to retain the 00:30:00 value but still join a second copy of the table.

Is this possible?

Schema: http://pastebin.com/0vC9pcqz

Upvotes: 0

Views: 74

Answers (1)

Andrey Ganin
Andrey Ganin

Reputation: 359

Use subquery 

SELECT r1.run AS run 
FROM ( 
    SELECT pattern.section AS section, SEC_TO_TIME(SUM(l1.elapsed)) AS run
    FROM pattern
    INNER JOIN link l1 ON l1.section = pattern.section
    GROUP BY pattern.service, pattern.direction, pattern.section
    WHERE pattern.service = "44-A-B-y10-1" AND pattern.direction = 'outbound'
    ) r1
INNER JOIN link l2 ON l2.section = r1.section

If You want get only 1 row use:

SELECT DISTINCT r1.run AS run 
FROM ( 
    SELECT pattern.section AS section, SEC_TO_TIME(SUM(l1.elapsed)) AS run
    FROM pattern
    INNER JOIN link l1 ON l1.section = pattern.section
    GROUP BY pattern.service, pattern.direction, pattern.section
    WHERE pattern.service = "44-A-B-y10-1" AND pattern.direction = 'outbound'
    ) r1
INNER JOIN link l2 ON l2.section = r1.section

Upvotes: 1

Related Questions