Conditional join - 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 2000 Forums

Transact-SQL (2000)

Conditional join

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2004-02-19 : 23:29:27

I dont even know if what I am trying to do qualifies as a "Conditional join", but that is what I am guessing. Three tables:(Entity, EntityCharge, And Type). What I need is a list of all EntityCharges summed together by Entity types. The kicker to this is that in EntityCharge, they can actually bill a different Entity for their charge. In that case, I would need to use the billing entity's type, not the entity's type. I have tried several case statements in different locations, none of which worked. I have created some simplified tables and sql statement listed below.

CREATE TABLE [dbo].[Type] (
[ID] [int] NOT NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

INSERT INTO Type (ID,Description) VAlues (1,'Resident')
INSERT INTO Type (ID,Description) VALUES (2,'Non-Resident')

CREATE TABLE [dbo].[EntityCharge] (
[ID] [int] NOT NULL ,
[EntityID] [int] NULL ,
[BillToEntityID] [int] NULL ,
[Amount] [money] NULL
) ON [PRIMARY]
GO

INSERT INTO EntityCharge (ID,EntityID,BillToEntityID,Amount) VAlues (1,1,2,20)

CREATE TABLE [dbo].[Entity] (
[ID] [int] NOT NULL ,
[LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TypeID] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO Entity (ID,LastName,TypeID) VAlues (1,'Smith',1)
INSERT INTO Entity (ID,LastName,TypeID) VALUES (2,'Doe',2)

Here is my current sql statement:

SELECT Sum(ec.Amount) as TypeTotal,
e.TypeID,
t.Description as TypeDescription
FROM EntityCharge as ec
INNER JOIN Entity as e on e.ID = ec.EntityID
INNER JOIN Type as t ON e.TypeID = t.ID
GROUP BY e.TypeID, t.Description

I can see my problem where I am joining the entity table and entity charge table on e.ID = ec.EntityID... where if ec.BillToEntityID has a value, then I want to join on that field. But I cannot figure out how to do that.

What is returned is
20.00 1 Resident

What I need returned is
20.00 2 Non-Resident

I am almost embarrased to post what I tried becuase it is quite evident that it is not possible to do this, but maybe it will give you some insight into what I was attempting and you can direct me in to the light.

SELECT Sum(ec.Amount) as TypeTotal,
e.TypeID,
t.Description as TypeDescription
FROM EntityCharge as ec
CASE
WHEN ec.BilltoEntityId is null or ec.BillToEntityID = 0
THEN INNER JOIN Entity as e on e.ID = ec.EntityID
Else INNER JOIN Entity as e on e.ID = ec.BillToEntityID
END IF
INNER JOIN Type as t ON e.TypeID = t.ID
GROUP BY e.TypeID, t.Description

THANK YOU THANK YOU in advance for any advice you may have.
JAdauto

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-02-19 : 23:45:06

JaDauto,

CASE is your friend....


SELECT Sum(ec.Amount) as TypeTotal, e.TypeID,t.Description as TypeDescription 
FROM EntityCharge as ec 
INNER JOIN Entity as e on e.ID = CASE WHEN BillToEntityID IS NULL THEN ec.EntityID ELSE BillToEntityID END
INNER JOIN Type as t ON e.TypeID = t.ID 
GROUP BY e.TypeID, t.Description

DavidM

"SQL-3 is an abomination.."

JAdauto
Posting Yak Master

160 Posts

Posted - 2004-02-20 : 00:00:47

I CANNOT BELIEVE IT! This works perfectly. I think I tried using this case statement everywhere in the sql statement... from the Select, to the Join to the Group... everywhere EXCEPT there.

Thanks so much!
JAdauto

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources