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!
MartinSELECT 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
--|||Thanks very much David!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:X4GdnRjemKI4mOndRVn-uA@.giganews.com...|||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
