Joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Joins

Author

Topic

slihp
Yak Posting Veteran

61 Posts

Posted - 2009-01-21 : 11:48:06

Hi all;

was sent some sql this week and came across a join conition ia have not see before, the sql is below.

SELECT
SUM(CASE WHEN TR_WORK_ACTIVITY.ACTIVITY_TYPE = 'CHRG' THEN NULLIF (TR_TIME_TXN.CHARGE, 0.0) ELSE NULL END) AS WIP_AMT
FROM
TR_WORK_ACTIVITY
RIGHT OUTER JOIN TR_TIME_TXN ON TR_WORK_ACTIVITY.ACTIVITY_CODE = TR_TIME_TXN.WORK_ACTIVITY
LEFT OUTER JOIN MATTER_MASTER ON TR_TIME_TXN.MATTER_DIWOR = MATTER_MASTER.DIWOR
LEFT OUTER JOIN BILL INNER JOIN BILL_ITEM ON BILL.DIWOR = BILL_ITEM.BILL_DIWOR AND
BILL.BILL_STATUS = 'P' ON TR_TIME_TXN.BILL_ITEM_DIWOR = BILL_ITEM.DIWOR
WHERE
(TR_TIME_TXN.STATUS = 'P') AND
MATTER_MASTER.MatterCode=244637 AND
TR_TIME_TXN.BILLED='Y'

the part i have never seen before is the last join.

LEFT OUTER JOIN BILL INNER JOIN BILL_ITEM ON BILL.DIWOR = BILL_ITEM.BILL_DIWOR AND
BILL.BILL_STATUS = 'P' ON TR_TIME_TXN.BILL_ITEM_DIWOR = BILL_ITEM.DIWOR

could anyone talk me through the last part, just to get my head to round this.

cheers,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 12:02:10

i think its missing on condition for left outer join. is this compliling fine?

slihp
Yak Posting Veteran

61 Posts

Posted - 2009-01-22 : 07:28:36

yes this compiles fine, and brings back what looks like the correct result set. but i have never seen the last left outer join which is why im scratching my head. is this correct?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-22 : 07:35:33

quote:
Originally posted by slihp

Hi all;

was sent some sql this week and came across a join conition ia have not see before, the sql is below.

SELECT
SUM(CASE WHEN TR_WORK_ACTIVITY.ACTIVITY_TYPE = 'CHRG' THEN NULLIF (TR_TIME_TXN.CHARGE, 0.0) ELSE NULL END) AS WIP_AMT
FROM
TR_WORK_ACTIVITY
RIGHT OUTER JOIN TR_TIME_TXN ON TR_WORK_ACTIVITY.ACTIVITY_CODE = TR_TIME_TXN.WORK_ACTIVITY
LEFT OUTER JOIN MATTER_MASTER ON TR_TIME_TXN.MATTER_DIWOR = MATTER_MASTER.DIWOR
LEFT OUTER JOIN BILL (u have missed on condtion) INNER JOIN BILL_ITEM ON BILL.DIWOR = BILL_ITEM.BILL_DIWOR AND
BILL.BILL_STATUS = 'P' ON AND TR_TIME_TXN.BILL_ITEM_DIWOR = BILL_ITEM.DIWOR
WHERE
(TR_TIME_TXN.STATUS = 'P') AND
MATTER_MASTER.MatterCode=244637 AND
TR_TIME_TXN.BILLED='Y'

the part i have never seen before is the last join.

LEFT OUTER JOIN BILL INNER JOIN BILL_ITEM ON BILL.DIWOR = BILL_ITEM.BILL_DIWOR AND
BILL.BILL_STATUS = 'P' ON TR_TIME_TXN.BILL_ITEM_DIWOR = BILL_ITEM.DIWOR

could anyone talk me through the last part, just to get my head to round this.

cheers,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 09:25:02

quote:
Originally posted by bklr

quote:
Originally posted by slihp

Hi all;

was sent some sql this week and came across a join conition ia have not see before, the sql is below.

SELECT
SUM(CASE WHEN TR_WORK_ACTIVITY.ACTIVITY_TYPE = 'CHRG' THEN NULLIF (TR_TIME_TXN.CHARGE, 0.0) ELSE NULL END) AS WIP_AMT
FROM
TR_WORK_ACTIVITY
RIGHT OUTER JOIN TR_TIME_TXN ON TR_WORK_ACTIVITY.ACTIVITY_CODE = TR_TIME_TXN.WORK_ACTIVITY
LEFT OUTER JOIN MATTER_MASTER ON TR_TIME_TXN.MATTER_DIWOR = MATTER_MASTER.DIWOR
LEFT OUTER JOIN BILL (u have missed on condtion) INNER JOIN BILL_ITEM ON BILL.DIWOR = BILL_ITEM.BILL_DIWOR AND
BILL.BILL_STATUS = 'P' ON AND TR_TIME_TXN.BILL_ITEM_DIWOR = BILL_ITEM.DIWOR
WHERE
(TR_TIME_TXN.STATUS = 'P') AND
MATTER_MASTER.MatterCode=244637 AND
TR_TIME_TXN.BILLED='Y'

the part i have never seen before is the last join.

LEFT OUTER JOIN BILL INNER JOIN BILL_ITEM ON BILL.DIWOR = BILL_ITEM.BILL_DIWOR AND
BILL.BILL_STATUS = 'P' ON TR_TIME_TXN.BILL_ITEM_DIWOR = BILL_ITEM.DIWOR

could anyone talk me through the last part, just to get my head to round this.

cheers,

why are you repeating what i suggested after so long?

slihp
Yak Posting Veteran

61 Posts

Posted - 2009-01-22 : 09:57:45

so is this how it should look.

SELECT
SUM(CASE WHEN TR_WORK_ACTIVITY.ACTIVITY_TYPE = 'CHRG' THEN NULLIF (TR_TIME_TXN.CHARGE, 0.0) ELSE NULL END) AS WIP_AMT
FROM
TR_WORK_ACTIVITY
RIGHT OUTER JOIN TR_TIME_TXN ON TR_WORK_ACTIVITY.ACTIVITY_CODE = TR_TIME_TXN.WORK_ACTIVITY
LEFT OUTER JOIN MATTER_MASTER ON TR_TIME_TXN.MATTER_DIWOR = MATTER_MASTER.DIWOR
LEFT OUTER JOIN BILL ON INNER JOIN BILL_ITEM ON BILL.DIWOR = BILL_ITEM.BILL_DIWOR AND
BILL.BILL_STATUS = 'P' AND TR_TIME_TXN.BILL_ITEM_DIWOR = BILL_ITEM.DIWOR
WHERE
(TR_TIME_TXN.STATUS = 'P') AND
MATTER_MASTER.MatterCode=244637 AND
TR_TIME_TXN.BILLED='Y'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 10:54:01

quote:
Originally posted by slihp

so is this how it should look.

SELECT
SUM(CASE WHEN TR_WORK_ACTIVITY.ACTIVITY_TYPE = 'CHRG' THEN NULLIF (TR_TIME_TXN.CHARGE, 0.0) ELSE NULL END) AS WIP_AMT
FROM
TR_WORK_ACTIVITY
RIGHT OUTER JOIN TR_TIME_TXN ON TR_WORK_ACTIVITY.ACTIVITY_CODE = TR_TIME_TXN.WORK_ACTIVITY
LEFT OUTER JOIN MATTER_MASTER ON TR_TIME_TXN.MATTER_DIWOR = MATTER_MASTER.DIWOR
LEFT OUTER JOIN BILL ON <condition here> INNER JOIN BILL_ITEM ON BILL.DIWOR = BILL_ITEM.BILL_DIWOR AND
BILL.BILL_STATUS = 'P' AND TR_TIME_TXN.BILL_ITEM_DIWOR = BILL_ITEM.DIWOR
WHERE
(TR_TIME_TXN.STATUS = 'P') AND
MATTER_MASTER.MatterCode=244637 AND
TR_TIME_TXN.BILLED='Y'

you're still missing condition for left join

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-22 : 23:13:37

quote:
Originally posted by visakh16

quote:
Originally posted by bklr

quote:
Originally posted by slihp

Hi all;

was sent some sql this week and came across a join conition ia have not see before, the sql is below.

SELECT
SUM(CASE WHEN TR_WORK_ACTIVITY.ACTIVITY_TYPE = 'CHRG' THEN NULLIF (TR_TIME_TXN.CHARGE, 0.0) ELSE NULL END) AS WIP_AMT
FROM
TR_WORK_ACTIVITY
RIGHT OUTER JOIN TR_TIME_TXN ON TR_WORK_ACTIVITY.ACTIVITY_CODE = TR_TIME_TXN.WORK_ACTIVITY
LEFT OUTER JOIN MATTER_MASTER ON TR_TIME_TXN.MATTER_DIWOR = MATTER_MASTER.DIWOR
LEFT OUTER JOIN BILL (u have missed on condtion) INNER JOIN BILL_ITEM ON BILL.DIWOR = BILL_ITEM.BILL_DIWOR AND
BILL.BILL_STATUS = 'P' ON AND TR_TIME_TXN.BILL_ITEM_DIWOR = BILL_ITEM.DIWOR
WHERE
(TR_TIME_TXN.STATUS = 'P') AND
MATTER_MASTER.MatterCode=244637 AND
TR_TIME_TXN.BILLED='Y'

the part i have never seen before is the last join.

LEFT OUTER JOIN BILL INNER JOIN BILL_ITEM ON BILL.DIWOR = BILL_ITEM.BILL_DIWOR AND
BILL.BILL_STATUS = 'P' ON TR_TIME_TXN.BILL_ITEM_DIWOR = BILL_ITEM.DIWOR

could anyone talk me through the last part, just to get my head to round this.

cheers,

why are you repeating what i suggested after so long?

i have strike the on condtion inbetween the and operator also see once

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources