Return a type

Hello All!
I would like to thank everyone for all the help, but.. (there is always a
but) i have another question.
I would like in my select a column displaying if the current line is a
Company or a Person, something like that:
SELECT *, "(Company or Person) As Type" FROM Client
LEFT JOIN Person ON Client.ID = Person.ID
LEFT JOIN Company ON Client.ID = Company.ID
I anyone could help me, i raelly would appreciate it!
thanks,
Bruno N
CREATE TABLE [Person] (
[Id] [int] NOT NULL ,
[RG] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Person_Customer] FOREIGN KEY
(
[Id]
) REFERENCES [Customer] (
[Id]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
CREATE TABLE [Customer] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [Company] (
[Id] [int] NOT NULL ,
[CNPJ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Company_Customer] FOREIGN KEY
(
[Id]
) REFERENCES [Customer] (
[Id]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]SELECT *,
CASE
WHEN Client.ID = Person.ID THEN 'Person'
WHEN Company.ID = Person.ID THEN 'Company'
ELSE ''
END
FROM
...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Bruno N" <nylren@.hotmail.com> wrote in message
news:u31HkjMJFHA.3136@.TK2MSFTNGP15.phx.gbl...
> Hello All!
> I would like to thank everyone for all the help, but.. (there is always a
> but) i have another question.
> I would like in my select a column displaying if the current line is a
> Company or a Person, something like that:
>
> SELECT *, "(Company or Person) As Type" FROM Client
> LEFT JOIN Person ON Client.ID = Person.ID
> LEFT JOIN Company ON Client.ID = Company.ID
>
> I anyone could help me, i raelly would appreciate it!
> thanks,
> Bruno N
>
> CREATE TABLE [Person] (
> [Id] [int] NOT NULL ,
> [RG] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Person_Customer] FOREIGN KEY
> (
> [Id]
> ) REFERENCES [Customer] (
> [Id]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
>
> CREATE TABLE [Customer] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
> CREATE TABLE [Company] (
> [Id] [int] NOT NULL ,
> [CNPJ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Company_Customer] FOREIGN KEY
> (
> [Id]
> ) REFERENCES [Customer] (
> [Id]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
>|||SELECT *,
Case When Person.ID Is Not Null then 'Person'
When Company.ID Is Not Null then 'Company'
When Customer.ID Is Not Null then 'Customer' ENd As Type
FROM Client
LEFT JOIN Person ON Client.ID = Person.ID
LEFT JOIN Company ON Client.ID = Company.ID
"Bruno N" wrote:

> Hello All!
> I would like to thank everyone for all the help, but.. (there is always a
> but) i have another question.
> I would like in my select a column displaying if the current line is a
> Company or a Person, something like that:
>
> SELECT *, "(Company or Person) As Type" FROM Client
> LEFT JOIN Person ON Client.ID = Person.ID
> LEFT JOIN Company ON Client.ID = Company.ID
>
> I anyone could help me, i raelly would appreciate it!
> thanks,
> Bruno N
>
> CREATE TABLE [Person] (
> [Id] [int] NOT NULL ,
> [RG] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Person_Customer] FOREIGN KEY
> (
> [Id]
> ) REFERENCES [Customer] (
> [Id]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
>
> CREATE TABLE [Customer] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
> CREATE TABLE [Company] (
> [Id] [int] NOT NULL ,
> [CNPJ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Company_Customer] FOREIGN KEY
> (
> [Id]
> ) REFERENCES [Customer] (
> [Id]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
>
>|||SELECT ...,
CASE
WHEN Person.ID IS NOT NULL THEN 'Person'
WHEN Company.ID IS NOT NULL THEN 'Company'
END, ...
You seem to be missing the alternate key on Customer Name. IDENTITY
should never be the only key of a table. Also, your constraints don't
prevent the same entity being entered as both Cutsomer and Company.
David Portas
SQL Server MVP
--