Change condition if the first doesn't exists

Have two tables:

code:

CREATE TABLE [Table1]
(
[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Number] [varchar] (50) NOT NULL ,
[TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
DEFAULT (getdate()),
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Table2]
(
[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Table1Id] [int] NOT NULL ,
[LingoId] [int] NOT NULL ,
[Header] [nvarchar] (150) NOT NULL ,
[Description] [ntext] NOT NULL ,
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
(
[Table1Id]
) REFERENCES [Table1] (
[Id]
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

What I would like is that if LingoId = 2 in the query below doesn't exists
than it should fall thru and use the values where LingoId = 1. LingoId = 1
always exists for each Table2.Id. Is this doable?

code:

SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
dbo.Table2.Header,
dbo.Table2.Description
FROM
dbo.Table1
LEFT OUTER JOIN
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
WHERE
(dbo.Table2.LingoId = 2) --Something should happen here I quess.

SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
(CASE WHEN T2_2.Existing IS NOT NULL THEN T2_2.Header ELSE
T2_1.Header END) as Header,
(CASE WHEN T2_2.Existing IS NOT NULL THEN T2_2.Description ELSE
T2_1.Description END) as Description
FROM
dbo.Table1
LEFT OUTER JOIN
(SELECT dbo.Table2.Header, dbo.Table2.Description, 'Exists'
Existing FROM Table2 Where Lingold = 2) T2_2
ON dbo.Table1.Id = T2_2.Table1Id
LEFT OUTER JOIN
(SELECT dbo.Table2.Header, dbo.Table2.Description, 'Exists'
Existing FROM Table2 Where Lingold = 1) T2_1
ON dbo.Table1.Id = T2_1.Table1Id
HTH, Jens Suessmeyer.|||Got an answer elsewhere that did the trick.
"Senna" wrote:

>
Have two tables:
>
>

code:

>
 CREATE TABLE [Table1]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Number] [varchar] (50) NOT NULL ,
>
 [TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
>
 DEFAULT (getdate()),
>
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY]
>
 ) ON [PRIMARY]
>
 GO
>
>
 CREATE TABLE [Table2]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Table1Id] [int] NOT NULL ,
>
 [LingoId] [int] NOT NULL ,
>
 [Header] [nvarchar] (150) NOT NULL ,
>
 [Description] [ntext] NOT NULL ,
>
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY] ,
>
 CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
>
 (
>
 [Table1Id]
>
 ) REFERENCES [Table1] (
>
 [Id]
>
 )
>
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
 GO
>

>
>
What I would like is that if LingoId = 2 in the query below doesn't exists
>
than it should fall thru and use the values where LingoId = 1. LingoId = 1
>
always exists for each Table2.Id. Is this doable?
>
>

code:

>
 SELECT
>
 dbo.Table1.Id,
>
 dbo.Table1.Number,
>
 dbo.Table1.[TimeStamp],
>
 dbo.Table2.Header,
>
 dbo.Table2.Description
>
 FROM
>
 dbo.Table1
>
 LEFT OUTER JOIN
>
 dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
>
 WHERE
>
 (dbo.Table2.LingoId = 2) --Something should happen here I quess.
>

|||First, you are filtering the result with values from table2. That will
effectively be an INNER JOIN. Did you mean it like that?
If you want to use only one query then something like this could be good
enough:
SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
dbo.Table2.Header,
dbo.Table2.Description
FROM
dbo.Table1
LEFT OUTER JOIN
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
WHERE
(dbo.Table2.LingoId = case when exists (select table1.id from table1
inner join table2 on table1.id = table2.id where table2.LingoID = 2) then 2
else 1 end)
MC
"Senna" <
Senna@.discussions.microsoft.com>
wrote in message
news:7335ADA4-EF90-4525-B513-ACD9F64CB353@.microsoft.com...
>
Have two tables:
>
>

code:

>
 CREATE TABLE [Table1]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Number] [varchar] (50) NOT NULL ,
>
 [TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
>
 DEFAULT (getdate()),
>
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY]
>
 ) ON [PRIMARY]
>
 GO
>
>
 CREATE TABLE [Table2]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Table1Id] [int] NOT NULL ,
>
 [LingoId] [int] NOT NULL ,
>
 [Header] [nvarchar] (150) NOT NULL ,
>
 [Description] [ntext] NOT NULL ,
>
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY] ,
>
 CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
>
 (
>
 [Table1Id]
>
 ) REFERENCES [Table1] (
>
 [Id]
>
 )
>
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
 GO
>

>
>
What I would like is that if LingoId = 2 in the query below doesn't exists
>
than it should fall thru and use the values where LingoId = 1. LingoId = 1
>
always exists for each Table2.Id. Is this doable?
>
>

code:

>
 SELECT
>
 dbo.Table1.Id,
>
 dbo.Table1.Number,
>
 dbo.Table1.[TimeStamp],
>
 dbo.Table2.Header,
>
 dbo.Table2.Description
>
 FROM
>
 dbo.Table1
>
 LEFT OUTER JOIN
>
 dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
>
 WHERE
>
 (dbo.Table2.LingoId = 2) --Something should happen here I quess.
>

|||The solution, as I mention above, looked liked this:
SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
dbo.Table2.Header,
dbo.Table2.Description
FROM
dbo.Table1
INNER JOIN
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
WHERE
dbo.Table2.LingoId = 2
or (dbo.Table2.LingoId = 1
and not exists
(select *
from table2 cn
where cn.table1id = dbo.Table2.Table1Id
and cn.lingoid = 2))
ps. Thank for your time and answer Jens.
"Senna" wrote:

>
Have two tables:
>
>

code:

>
 CREATE TABLE [Table1]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Number] [varchar] (50) NOT NULL ,
>
 [TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
>
 DEFAULT (getdate()),
>
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY]
>
 ) ON [PRIMARY]
>
 GO
>
>
 CREATE TABLE [Table2]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Table1Id] [int] NOT NULL ,
>
 [LingoId] [int] NOT NULL ,
>
 [Header] [nvarchar] (150) NOT NULL ,
>
 [Description] [ntext] NOT NULL ,
>
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY] ,
>
 CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
>
 (
>
 [Table1Id]
>
 ) REFERENCES [Table1] (
>
 [Id]
>
 )
>
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
 GO
>

>
>
What I would like is that if LingoId = 2 in the query below doesn't exists
>
than it should fall thru and use the values where LingoId = 1. LingoId = 1
>
always exists for each Table2.Id. Is this doable?
>
>

code:

>
 SELECT
>
 dbo.Table1.Id,
>
 dbo.Table1.Number,
>
 dbo.Table1.[TimeStamp],
>
 dbo.Table2.Header,
>
 dbo.Table2.Description
>
 FROM
>
 dbo.Table1
>
 LEFT OUTER JOIN
>
 dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
>
 WHERE
>
 (dbo.Table2.LingoId = 2) --Something should happen here I quess.
>

|||Yes, it was an inner join. :)
See the other post with the solution I went with. Thanks anyway for your
time and effort.
"MC" wrote:

> First, you are filtering the result with values from table2. That will
> effectively be an INNER JOIN. Did you mean it like that?
> If you want to use only one query then something like this could be good
> enough:
> SELECT
> dbo.Table1.Id,
> dbo.Table1.Number,
> dbo.Table1.[TimeStamp],
> dbo.Table2.Header,
> dbo.Table2.Description
> FROM
> dbo.Table1
> LEFT OUTER JOIN
> dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
> WHERE
> (dbo.Table2.LingoId = case when exists (select table1.id from table1
> inner join table2 on table1.id = table2.id where table2.LingoID = 2) then
2
> else 1 end)
>
> MC
>
> "Senna" <Senna@.discussions.microsoft.com> wrote in message
> news:7335ADA4-EF90-4525-B513-ACD9F64CB353@.microsoft.com...
>
>