IF Condition in Join?

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!

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

IDENTITY_INSERT

dear jeff johnson ,
thanks for ur response.
I have one table name fixtures in client machine .In that table one column
is identity type .now i am insert one Row from server database to that clien
t
database
now i have to on the identity_insert in client machine from server machine
give some suggestion.
sangeetha-server machine
sankar--client machine
i am running this query from my machine my machine name is sangeeta
EXEC master.dbo.xp_cmdshell 'osql -U scoremate -P scoremate -S sankar -Q
"set identity_insert scoremate.dbo.fixtures on "'
insert into openrowset('MSDASQL','DRIVER={SQL
Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
'select * from scoremate.dbo.fixtures')
select compcode,seasonid,matchid,matchdt,
time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
ground,umpires,genuser,
gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
openrowset('MSDASQL','DRIVER={SQL
Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
'select compcode,seasonid,matchid=15,matchdt,
time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
ground,umpires,genuser,
gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
scoremate.dbo.fixtures where matchid=13')Hi
Everything seems to point to the client machine?
You may be better off connecting to the server where you are doing the
inserts and not using your OPENROWSET as the destination of the insert.
John
"MOHAMED NASEER" wrote:

> dear jeff johnson ,
> thanks for ur response.
> I have one table name fixtures in client machine .In that table one colum
n
> is identity type .now i am insert one Row from server database to that cli
ent
> database
> now i have to on the identity_insert in client machine from server machine
> give some suggestion.
> sangeetha-server machine
> sankar--client machine
> i am running this query from my machine my machine name is sangeeta
> EXEC master.dbo.xp_cmdshell 'osql -U scoremate -P scoremate -S sankar -Q
> "set identity_insert scoremate.dbo.fixtures on "'
> insert into openrowset('MSDASQL','DRIVER={SQL
> Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
> 'select * from scoremate.dbo.fixtures')
> select compcode,seasonid,matchid,matchdt,
> time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
> ground,umpires,genuser,
> gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
> openrowset('MSDASQL','DRIVER={SQL
> Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
> 'select compcode,seasonid,matchid=15,matchdt,
> time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
> ground,umpires,genuser,
> gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
> scoremate.dbo.fixtures where matchid=13')sql

Identity seed lost...?

Dear all,
The last value I see for a identity field is 174. That's fine.
But the next value after insert which appears is 217 instead of 175. How do
I force the sequence 'natural' again'
I suppose that it happen due to I deleted some rows...
I would need in order to add a new row into a another table.
Thanks in advance,
EnricEnric
SET IDENTITY_INSERT
Be aware that an IDENTITY property may have gaps as well , and if it is
important , you can change to the natural key and add value to maximal key
SELECT COALESCE(max(col),0)+1 FROM Table WITH (UPDLOCK,HOLDLOCK)
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:61187E6F-DC85-4E17-B114-11283BD50B64@.microsoft.com...
> Dear all,
> The last value I see for a identity field is 174. That's fine.
> But the next value after insert which appears is 217 instead of 175. How
> do
> I force the sequence 'natural' again'
> I suppose that it happen due to I deleted some rows...
> I would need in order to add a new row into a another table.
> Thanks in advance,
> Enric
>|||Thanks for your post, anyway I will not know which will be the next value in
case I delete some rows.
"Uri Dimant" wrote:
> Enric
> SET IDENTITY_INSERT
> Be aware that an IDENTITY property may have gaps as well , and if it is
> important , you can change to the natural key and add value to maximal k
ey
> SELECT COALESCE(max(col),0)+1 FROM Table WITH (UPDLOCK,HOLDLOCK)
>
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:61187E6F-DC85-4E17-B114-11283BD50B64@.microsoft.com...
>
>|||Try this ...
DBCC CHECKIDENT(tablename, RESEED, 0)
DBCC CHECKIDENT(tablename, RESEED)
"Enric" wrote:

> Dear all,
> The last value I see for a identity field is 174. That's fine.
> But the next value after insert which appears is 217 instead of 175. How d
o
> I force the sequence 'natural' again'
> I suppose that it happen due to I deleted some rows...
> I would need in order to add a new row into a another table.
> Thanks in advance,
> Enric
>|||"Enric" wrote:
> Thanks for your post, anyway I will not know which will be the next value
in
> case I delete some rows.
> "Uri Dimant" wrote:
If you want a gapless sequence then IDENTITY is the wrong solution. Don't
use IDENTITY in a way that has meaning for your users precisely because you
can't always control the IDENTITY value. IDENTITY is intended to be used as
an artificial surrogate key only.
Why do you need an IDENTITY column and why do you care if the sequence has
gaps?
David Portas
SQL Server MVP
--

IDENTITY reaches the max value

Dear All,
what happens if IDENTITY value reaches the max value?
for example if we decide for a table to have an integer and the counter
exceeded the maximmum value how whould it reacte
Rami,> what happens if IDENTITY value reaches the max value?
You will get an overflow error.
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
You'll need to create a new table with the desired schema (e.g. bigint for
the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
turned on. For example:
CREATE TABLE OldTable
(
IdentityColumn int NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
INSERT INTO OldTable (OtherData) VALUES(1)
INSERT INTO OldTable (OtherData) VALUES(2)
INSERT INTO OldTable (OtherData) VALUES(3)
GO
CREATE TABLE NewTable
(
IdentityColumn bigint NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
GO
SET IDENTITY_INSERT NewTable ON
GO
INSERT INTO NewTable (IdentityColumn, OtherData)
SELECT IdentityColumn, OtherData FROM OldTable
GO
SET IDENTITY_INSERT NewTable OFF
GO
DROP TABLE OldTable
GO
EXEC sp_rename 'NewTable', 'OldTable'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
> Dear All,
> what happens if IDENTITY value reaches the max value?
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
> Rami,|||Dear Dan Guzman,
It is working fine Thanks alot Dan
Best wishes
Rami,
"Dan Guzman" wrote:
> > what happens if IDENTITY value reaches the max value?
> You will get an overflow error.
> > for example if we decide for a table to have an integer and the counter
> > exceeded the maximmum value how whould it reacte
> You'll need to create a new table with the desired schema (e.g. bigint for
> the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
> turned on. For example:
> CREATE TABLE OldTable
> (
> IdentityColumn int NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> INSERT INTO OldTable (OtherData) VALUES(1)
> INSERT INTO OldTable (OtherData) VALUES(2)
> INSERT INTO OldTable (OtherData) VALUES(3)
> GO
> CREATE TABLE NewTable
> (
> IdentityColumn bigint NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> GO
> SET IDENTITY_INSERT NewTable ON
> GO
> INSERT INTO NewTable (IdentityColumn, OtherData)
> SELECT IdentityColumn, OtherData FROM OldTable
> GO
> SET IDENTITY_INSERT NewTable OFF
> GO
> DROP TABLE OldTable
> GO
> EXEC sp_rename 'NewTable', 'OldTable'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
> > Dear All,
> >
> > what happens if IDENTITY value reaches the max value?
> >
> > for example if we decide for a table to have an integer and the counter
> > exceeded the maximmum value how whould it reacte
> >
> > Rami,
>
>

IDENTITY reaches the max value

Dear All,
what happens if IDENTITY value reaches the max value?
for example if we decide for a table to have an integer and the counter
exceeded the maximmum value how whould it reacte
Rami,
> what happens if IDENTITY value reaches the max value?
You will get an overflow error.

> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
You'll need to create a new table with the desired schema (e.g. bigint for
the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
turned on. For example:
CREATE TABLE OldTable
(
IdentityColumn int NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
INSERT INTO OldTable (OtherData) VALUES(1)
INSERT INTO OldTable (OtherData) VALUES(2)
INSERT INTO OldTable (OtherData) VALUES(3)
GO
CREATE TABLE NewTable
(
IdentityColumn bigint NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
GO
SET IDENTITY_INSERT NewTable ON
GO
INSERT INTO NewTable (IdentityColumn, OtherData)
SELECT IdentityColumn, OtherData FROM OldTable
GO
SET IDENTITY_INSERT NewTable OFF
GO
DROP TABLE OldTable
GO
EXEC sp_rename 'NewTable', 'OldTable'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
> Dear All,
> what happens if IDENTITY value reaches the max value?
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
> Rami,
|||Dear Dan Guzman,
It is working fine Thanks alot Dan
Best wishes
Rami,
"Dan Guzman" wrote:

> You will get an overflow error.
>
> You'll need to create a new table with the desired schema (e.g. bigint for
> the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
> turned on. For example:
> CREATE TABLE OldTable
> (
> IdentityColumn int NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> INSERT INTO OldTable (OtherData) VALUES(1)
> INSERT INTO OldTable (OtherData) VALUES(2)
> INSERT INTO OldTable (OtherData) VALUES(3)
> GO
> CREATE TABLE NewTable
> (
> IdentityColumn bigint NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> GO
> SET IDENTITY_INSERT NewTable ON
> GO
> INSERT INTO NewTable (IdentityColumn, OtherData)
> SELECT IdentityColumn, OtherData FROM OldTable
> GO
> SET IDENTITY_INSERT NewTable OFF
> GO
> DROP TABLE OldTable
> GO
> EXEC sp_rename 'NewTable', 'OldTable'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
>
>

IDENTITY reaches the max value

Dear All,
what happens if IDENTITY value reaches the max value?
for example if we decide for a table to have an integer and the counter
exceeded the maximmum value how whould it reacte
Rami,> what happens if IDENTITY value reaches the max value?
You will get an overflow error.

> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
You'll need to create a new table with the desired schema (e.g. bigint for
the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
turned on. For example:
CREATE TABLE OldTable
(
IdentityColumn int NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
INSERT INTO OldTable (OtherData) VALUES(1)
INSERT INTO OldTable (OtherData) VALUES(2)
INSERT INTO OldTable (OtherData) VALUES(3)
GO
CREATE TABLE NewTable
(
IdentityColumn bigint NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
GO
SET IDENTITY_INSERT NewTable ON
GO
INSERT INTO NewTable (IdentityColumn, OtherData)
SELECT IdentityColumn, OtherData FROM OldTable
GO
SET IDENTITY_INSERT NewTable OFF
GO
DROP TABLE OldTable
GO
EXEC sp_rename 'NewTable', 'OldTable'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
> Dear All,
> what happens if IDENTITY value reaches the max value?
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
> Rami,|||Dear Dan Guzman,
It is working fine Thanks alot Dan
Best wishes
Rami,
"Dan Guzman" wrote:

> You will get an overflow error.
>
> You'll need to create a new table with the desired schema (e.g. bigint for
> the IDENTITY column) and insert data into the new table with IDENTITY_INSE
RT
> turned on. For example:
> CREATE TABLE OldTable
> (
> IdentityColumn int NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> INSERT INTO OldTable (OtherData) VALUES(1)
> INSERT INTO OldTable (OtherData) VALUES(2)
> INSERT INTO OldTable (OtherData) VALUES(3)
> GO
> CREATE TABLE NewTable
> (
> IdentityColumn bigint NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> GO
> SET IDENTITY_INSERT NewTable ON
> GO
> INSERT INTO NewTable (IdentityColumn, OtherData)
> SELECT IdentityColumn, OtherData FROM OldTable
> GO
> SET IDENTITY_INSERT NewTable OFF
> GO
> DROP TABLE OldTable
> GO
> EXEC sp_rename 'NewTable', 'OldTable'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
>
>