| Author |
 Topic  |
|
|
dgaylor
Yak Posting Veteran
54 Posts |
 Posted - 09/02/2007 : 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
Sweden
29138 Posts |
Posted - 09/02/2007 : 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
355 Posts |
Posted - 09/03/2007 : 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
|
Edited by - PeterNeo on 09/03/2007 02:35:58 |
|
|
|
Koji Matsumura
Posting Yak Master
141 Posts |
Posted - 09/03/2007 : 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 - 09/03/2007 : 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 - 09/03/2007 : 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
Slovenia
11741 Posts |
|
| |
Topic |
|
|
|
Join whether exists or not
