CODEWITHSUNDEEP
sqlsql-serverleft-join
Necvetanov
Necvetanov

Reputation: 374

Left Join on the same table basics

Im trying to create the following table:

JpId | JpName | JpValue | JpEndvalue Where JpEndValue is JpValue (TimeStamp + 1day) from the same table but I get this result:

Table:

JpId JpName JpTimeStamp JpValue

1   MAGIC   2017-06-15  151
2   BASIC   2017-06-15  152
3   MINI    2017-06-15  153
4   SUPER   2017-06-15  154
5   MAGIC   2017-06-16  161
6   BASIC   2017-06-16  162
7   MINI    2017-06-16  163
8   SUPER   2017-06-16  164

Query

SELECT jp1.JpId
    ,jp1.JpName
    ,jp1.JpValue
    ,jp2.JpValue AS 'JpEndValue'
FROM jackpot_web_report jp1
LEFT JOIN jackpot_web_report jp2
    ON jp2.JpTimeStamp = '2017-06-16'
WHERE jp1.JpTimeStamp = '2017-06-15';

Result:

1   MAGIC   151 161
1   MAGIC   151 162
1   MAGIC   151 163
1   MAGIC   151 164
2   BASIC   152 161
2   BASIC   152 162
2   BASIC   152 163
2   BASIC   152 164
3   MINI    153 161
3   MINI    153 162
3   MINI    153 163
3   MINI    153 164
4   SUPER   154 161
4   SUPER   154 162
4   SUPER   154 163
4   SUPER   154 164

Expected result:

1   MAGIC   151 161
2   BASIC   152 162
3   MINI    153 163
4   SUPER   154 164

Upvotes: 1

Views: 60

Answers (1)

Mikhail Lobanov
Mikhail Lobanov

Reputation: 3026

I seems you missed jp2.JpName = jp1.JpName condition:

SELECT jp1.JpId
    ,jp1.JpName
    ,jp1.JpValue
    ,jp2.JpValue AS 'JpEndValue'
FROM jackpot_web_report jp1
LEFT JOIN jackpot_web_report jp2
    ON jp2.JpTimeStamp = '2017-06-16' AND jp2.JpName = jp1.JpName
WHERE jp1.JpTimeStamp = '2017-06-15';

you can also add DATEADD in join condition:

SELECT jp1.JpId
    ,jp1.JpName
    ,jp1.JpValue
    ,jp2.JpValue AS 'JpEndValue'
FROM jackpot_web_report jp1
LEFT JOIN jackpot_web_report jp2
    ON jp2.JpTimeStamp = DATEADD(DAY, 1, jp1.JpTimeStamp) AND jp2.JpName = jp1.JpName
WHERE jp1.JpTimeStamp = '2017-06-15';

Upvotes: 2

Related Questions