Join Query

Hi,
I have 3 tables as follows (simplified for posting here):
Table1
--
CREATE TABLE [dbo].[Customer] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerName] [varchar] (50) NOT NULL
) ON [PRIMARY]
Table2
--
CREATE TABLE [dbo].[Supplier] (
[SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
[SupplierName] [varchar] (50) NOT NULL
) ON [PRIMARY]
Table3
--
CREATE TABLE [dbo].[PhoneBook] (
[PBID] [int] IDENTITY (1, 1) NOT NULL ,
[PhoneNumber] [varchar] (50) NOT NULL ,
[ContactType] [varchar] (10) NOT NULL ,
[ContactID] [int] NOT NULL
) ON [PRIMARY]
The ContactType column in Table3 (Supplier) has 2 possible values
(Customer and Supplier) and the ContactID column will either have
CustomerID or SupplierID stored in it based on the ContactType.
Now I am trying to write a query that will return me all records from
the 3rd table (PhoneBook) along with the original names. I have come
up with this:
Query:
--
Select
P.PBID,
P.PhoneNumber ,
IsNull(C.CustomerName, '') + IsNull(S.SupplierName, '') As
ContactName,
P.Contacttype
From
PhoneBook P
Left Outer Join Customer C On P.ContactID = C.CustomerID and
P.Contacttype = 'Customer'
Left Outer Join Supplier S On P.ContactID = S.SupplierID and
P.Contacttype = 'Supplier'
Obviously I'm new to writing queries and I know that there is probably
a much better way to write this query. This query seems inelegant and
probably very poorly written. I am hoping someone can help me come up
with a better queries.
Thanks in advance!
AbhinavHi,
You can use the CASE expression as bellow:
Select
P.PBID,
P.PhoneNumber ,
Case When P.Contacttype = 'Customer' Then C.CustomerName Else S.SupplierName
End As
ContactName,
P.Contacttype
From
PhoneBook P
Left Outer Join Customer C On P.ContactID = C.CustomerID
Left Outer Join Supplier S On P.ContactID = S.SupplierID
Regards,
arik.
"abhinav50@.gmail.com" wrote:

> Hi,
> I have 3 tables as follows (simplified for posting here):
> Table1
> --
> CREATE TABLE [dbo].[Customer] (
> [CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
>
> Table2
> --
> CREATE TABLE [dbo].[Supplier] (
> [SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
> [SupplierName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
>
> Table3
> --
> CREATE TABLE [dbo].[PhoneBook] (
> [PBID] [int] IDENTITY (1, 1) NOT NULL ,
> [PhoneNumber] [varchar] (50) NOT NULL ,
> [ContactType] [varchar] (10) NOT NULL ,
> [ContactID] [int] NOT NULL
> ) ON [PRIMARY]
>
> The ContactType column in Table3 (Supplier) has 2 possible values
> (Customer and Supplier) and the ContactID column will either have
> CustomerID or SupplierID stored in it based on the ContactType.
> Now I am trying to write a query that will return me all records from
> the 3rd table (PhoneBook) along with the original names. I have come
> up with this:
> Query:
> --
> Select
> P.PBID,
> P.PhoneNumber ,
> IsNull(C.CustomerName, '') + IsNull(S.SupplierName, '') As
> ContactName,
> P.Contacttype
> From
> PhoneBook P
> Left Outer Join Customer C On P.ContactID = C.CustomerID and
> P.Contacttype = 'Customer'
> Left Outer Join Supplier S On P.ContactID = S.SupplierID and
> P.Contacttype = 'Supplier'
> Obviously I'm new to writing queries and I know that there is probably
> a much better way to write this query. This query seems inelegant and
> probably very poorly written. I am hoping someone can help me come up
> with a better queries.
> Thanks in advance!
> Abhinav
>