Join whether exists or not - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Join whether exists or not

Author

Topic

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2007-09-02 : 14:32:14

Hi, I have the following 2 tables which contain text that I want to display in a grid. The WebTextKeys table contains all of the text keys.

WebText contains the actual text, in different languages, indicated by the LanguageID, and english (LanguageID = 1) will always have an entry for each webtextkeyid).

What I would like to do is return an entry for the english record, joined with the other language record whether it exists for not

So, if I create a query and set @LanguageID = '2' I would like the following to be returned:

WebTextKeyID_English,WebTextID_English,Text_English,WebTextID,Text
------------------------------------------------------------------
1,1,Name,1,Name
1,1,Name,2,Nom
2,3,Address,3,Address
2,3,Address,NULL,NULL
3,4,Country,4,Country
3,4,Country,NULL,NULL

Is this possible to do? Thanks in advance.

CREATE TABLE [dbo].[WebTextKeys] (
[WebTextKeyID] [int] ,
[TextKey] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WebText] (
[WebTextID] [int],
[WebTextKeyID] [int] ,
[LanguageID] [int],
[Text] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
) ON [PRIMARY]
GO

INSERT WebTextKeys (WebTextKeyID, TextKey)
VALUES (1, 'Name')
INSERT WebTextKeys (WebTextKeyID, TextKey)
VALUES (2, 'Address')
INSERT WebTextKeys (WebTextKeyID, TextKey)
VALUES (3, 'Country')
GO

INSERT WebText (WebTextID, WebTextKeyID, LanguageID, Text)
VALUES (1,1,1,'Name')
INSERT WebText (WebTextID, WebTextKeyID, LanguageID, Text)
VALUES (2,1,2,'Nom')
INSERT WebText (WebTextID, WebTextKeyID, LanguageID, Text)
VALUES (3,2,1,'Address')
INSERT WebText (WebTextID, WebTextKeyID, LanguageID, Text)
VALUES (4,3,1,'Country')
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-02 : 15:35:19

Use LEFT JOIN. If you are unsure what LEFT (OUTER) JOIN is, please read Books Online.

E 12°55'05.25"
N 56°04'39.16"

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-09-03 : 01:38:15

Hi,

I am not able to get the records
2,3,Address,NULL,NULL
3,4,Country,NULL,NULL

but remaining is correct, check it once

DECLARE @WebTextKeys TABLE
(
[WebTextKeyID] [int] ,
[TextKey] [nvarchar] (50)
)

DECLARE @WebText TABLE
(
[WebTextID] [int],
[WebTextKeyID] [int] ,
[LanguageID] [int],
[Text] [nvarchar] (2000)
)

INSERT @WebTextKeys (WebTextKeyID, TextKey)
VALUES (1, 'Name')
INSERT @WebTextKeys (WebTextKeyID, TextKey)
VALUES (2, 'Address')
INSERT @WebTextKeys (WebTextKeyID, TextKey)
VALUES (3, 'Country')

INSERT @WebText (WebTextID, WebTextKeyID, LanguageID, Text)
VALUES (1,1,1,'Name')
INSERT @WebText (WebTextID, WebTextKeyID, LanguageID, Text)
VALUES (2,1,2,'Nom')
INSERT @WebText (WebTextID, WebTextKeyID, LanguageID, Text)
VALUES (3,2,1,'Address')
INSERT @WebText (WebTextID, WebTextKeyID, LanguageID, Text)
VALUES (4,3,1,'Country')

--SELECT * FROM @WebText
--SELECT * FROM @WebTextKeys

SELECT t.WebTextKeyID, e.WebTextId, e.Text,
l.WebTextId, l.Text
FROM @WebTextKeys t
LEFT JOIN @WebText e ON e.WebTextKeyID = t.WebTextKeyID
AND e.Languageid = 1
LEFT JOIN @WebText l ON l.WebTextKeyID = t.WebTextKeyID

UNION

SELECT t.WebTextKeyID, e.WebTextId, e.Text,
l.WebTextId, l.Text
FROM @WebTextKeys t
LEFT JOIN @WebText e ON e.WebTextKeyID = t.WebTextKeyID
AND e.Languageid = 1
LEFT JOIN @WebText l ON l.WebTextKeyID = t.WebTextKeyID
AND l.Languageid <> 1

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-09-03 : 02:24:54

I'm not sure how you want WebTextID_English to be, but the following should get you started.

DECLARE @WebTextKeys TABLE (WebTextKeyID int, TextKey nvarchar(50))
DECLARE @WebText TABLE (WebTextID int, WebTextKeyID int, LanguageID int, Text nvarchar(2000))

INSERT @WebTextKeys (WebTextKeyID, TextKey) VALUES (1, 'Name')
INSERT @WebTextKeys (WebTextKeyID, TextKey) VALUES (2, 'Address')
INSERT @WebTextKeys (WebTextKeyID, TextKey) VALUES (3, 'Country')

INSERT @WebText (WebTextID, WebTextKeyID, LanguageID, Text) VALUES (1,1,1,'Name')
INSERT @WebText (WebTextID, WebTextKeyID, LanguageID, Text) VALUES (2,1,2,'Nom')
INSERT @WebText (WebTextID, WebTextKeyID, LanguageID, Text) VALUES (3,2,1,'Address')
INSERT @WebText (WebTextID, WebTextKeyID, LanguageID, Text) VALUES (4,3,1,'Country')

SELECT * FROM @WebText
SELECT * FROM @WebTextKeys

DECLARE @LanguageID int
SELECT @LanguageID = 2

SELECT A.WebTextKeyID AS WebTextKeyID_English, A.TextKey AS Text_English, B.WebTextID, B.Text
FROM @WebTextKeys A
LEFT OUTER JOIN @WebText B ON B.WebTextKeyID = A.WebTextKeyID AND B.LanguageID = 1
UNION
SELECT A.WebTextKeyID, A.TextKey, B.WebTextID, B.Text
FROM @WebTextKeys A
LEFT OUTER JOIN @WebText B ON B.WebTextKeyID = A.WebTextKeyID AND B.LanguageID = @LanguageID

ranganath
Posting Yak Master

209 Posts

Posted - 2007-09-03 : 02:35:23

Hi,

DEclare @WebTextKeys TABLE (
[WebTextKeyID] [int] ,
[TextKey] [nvarchar] (50)
)

declare @webText TABLE (
[WebTextID] [int],
[WebTextKeyID] [int] ,
[LanguageID] [int],
[Text] [nvarchar] (2000)
)

INSERT @WebTextKeys (WebTextKeyID, TextKey)
VALUES (1, 'Name')
INSERT @WebTextKeys (WebTextKeyID, TextKey)
VALUES (2, 'Address')
INSERT @WebTextKeys (WebTextKeyID, TextKey)
VALUES (3, 'Country')

Select * From @WebTextKeys

INSERT @WebText (WebTextID, WebTextKeyID, LanguageID, Text)
VALUES (1,1,1,'Name')
INSERT @WebText (WebTextID, WebTextKeyID, LanguageID, Text)
VALUES (2,1,2,'Nom')
INSERT @WebText (WebTextID, WebTextKeyID, LanguageID, Text)
VALUES (3,2,1,'Address')
INSERT @WebText (WebTextID, WebTextKeyID, LanguageID, Text)
VALUES (4,3,1,'Country')

Select * From @WebText

Declare @Temp Table (WebTextKeyID int, WebTextID int, text varchar(100),WebTextID1 int, text1 varchar(100) )

Insert into @Temp

Select distinct A.WebTextKeyID, B.WebTextID,B.text,
B1.WebTextID,B1.text
From @WebTextKeys A
Left join @WebText B on A.WebTextKeyID = B.WebTextkeyID and b.languageId = 1
Left join @WebText B1 on A.WebTextKeyID = B1.WebTextkeyID

union all

Select distinct A.WebTextKeyID, B1.WebTextID,B1.text,
B.WebTextID,B.text
From @WebTextKeys A
Left join @WebText B on A.WebTextKeyID = B.WebTextkeyID and b.languageId <>1
Left join @WebText B1 on A.WebTextKeyID = B1.WebTextkeyID and b1.languageId =1

Select Distinct WebTextKeyID , WebTextID , [text] ,WebTextID1 , text1 From @Temp

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-09-03 : 03:31:56

quote:
Originally posted by Koji Matsumura

I'm not sure how you want WebTextID_English to be, but the following should get you started.

DECLARE @WebTextKeys TABLE (WebTextKeyID int, TextKey nvarchar(50))
DECLARE @WebText TABLE (WebTextID int, WebTextKeyID int, LanguageID int, Text nvarchar(2000))

INSERT @WebTextKeys (WebTextKeyID, TextKey) VALUES (1, 'Name')
INSERT @WebTextKeys (WebTextKeyID, TextKey) VALUES (2, 'Address')
INSERT @WebTextKeys (WebTextKeyID, TextKey) VALUES (3, 'Country')

INSERT @WebText (WebTextID, WebTextKeyID, LanguageID, Text) VALUES (1,1,1,'Name')
INSERT @WebText (WebTextID, WebTextKeyID, LanguageID, Text) VALUES (2,1,2,'Nom')
INSERT @WebText (WebTextID, WebTextKeyID, LanguageID, Text) VALUES (3,2,1,'Address')
INSERT @WebText (WebTextID, WebTextKeyID, LanguageID, Text) VALUES (4,3,1,'Country')

SELECT * FROM @WebText
SELECT * FROM @WebTextKeys

DECLARE @LanguageID int
SELECT @LanguageID = 2

SELECT A.WebTextKeyID AS WebTextKeyID_English, A.TextKey AS Text_English, B.WebTextID, B.Text
FROM @WebTextKeys A
LEFT OUTER JOIN @WebText B ON B.WebTextKeyID = A.WebTextKeyID AND B.LanguageID = 1
UNION
SELECT A.WebTextKeyID, A.TextKey, B.WebTextID, B.Text
FROM @WebTextKeys A
LEFT OUTER JOIN @WebText B ON B.WebTextKeyID = A.WebTextKeyID AND B.LanguageID = @LanguageID

This will get you WebTextID_English.

SELECT A.WebTextKeyID AS WebTextKeyID_English, B.WebTextID AS WebTextID_English, A.TextKey AS Text_English, B.WebTextID, B.Text
FROM @WebTextKeys A
LEFT OUTER JOIN @WebText B ON B.WebTextKeyID = A.WebTextKeyID AND B.LanguageID = 1
UNION
SELECT A.WebTextKeyID, C.WebTextID, A.TextKey, B.WebTextID, B.Text
FROM @WebTextKeys A
LEFT OUTER JOIN @WebText B ON B.WebTextKeyID = A.WebTextKeyID AND B.LanguageID = @LanguageID
INNER JOIN @WebText C ON C.WebTextKeyID = A.WebTextKeyID AND C.LanguageID = 1

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-03 : 04:33:57

read this:
http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources