IF Condition in Join??

[Martin Feuersteiner]

Dear Group

I'd be grateful if you can send me on the right track in achieving this.

I have three tables A,B,C outlined as follows:

Table: A
Field: RowID
Field: EntityID
Field: TypeIdentifier

Table: B
Field: RowID
Field: Name

Table: C
Field: RowID
Field: Name

Let's assume I've the following records:

Table A:
1,1,0
2,1,1

Table B:
1,Smith

Table C:
1,XYZCorporation

The table joins are as follows:

A.EntityID = B.RowID
A.EntityID = C.RowID

I would like to select all records from Table A and display the Names from
either Table B or Table C, depending on the Field TypeIdentifier.
E.g.: SELECT Name FROM A JOIN B ON (A.EntityID = B.RowID) JOIN C ON
(A.EntityID = C.RowID) IF TypeIdentifier = 0 SELECT Name FROM B IF
TypeIdentifier = 1 SELECT Name FROM C

Resultset:

Smith
XYZCorporation

Is this somehow possible?

Thanks very much for your time & efforts!

Martin

[David Portas]

SELECT COALESCE(B.name,C.name) AS name
FROM A
LEFT JOIN B
ON A.entityid = B.rowid
AND A.typeidentifier = 1
LEFT JOIN C
ON A.entityid = C.rowid
AND A.typeidentifier = 0

--
David Portas
SQL Server MVP
--

[Martin Feuersteiner]

Thanks very much David!

"David Portas" <RE****************************@acm.org> wrote in message
news:X4********************@giganews.com...

[William Cleveland]

In the past, I have done something like this:

LEFT JOIN B ON A.entityid = B.rowid
LEFT JOIN C ON A.entityid = C.rowid"

with a Case statement in the select. Your version
is much nicer, thanks.

Bill

David Portas wrote:

SELECT COALESCE(B.name,C.name) AS name
FROM A
LEFT JOIN B
ON A.entityid = B.rowid
AND A.typeidentifier = 1
LEFT JOIN C
ON A.entityid = C.rowid
AND A.typeidentifier = 0