SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Join whether exists or not
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dgaylor
Yak Posting Veteran

54 Posts

Posted - 09/02/2007 :  14:32:14  Show Profile  Reply with Quote
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
30249 Posts

Posted - 09/02/2007 :  15:35:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 09/03/2007 :  01:38:15  Show Profile  Reply with Quote
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
Go to Top of Page

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 09/03/2007 :  02:24:54  Show Profile  Reply with Quote
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
Go to Top of Page

ranganath
Posting Yak Master

209 Posts

Posted - 09/03/2007 :  02:35:23  Show Profile  Reply with Quote
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

Go to Top of Page

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 09/03/2007 :  03:31:56  Show Profile  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 09/03/2007 :  04:33:57  Show Profile  Visit spirit1's Homepage  Reply with Quote
read this:
http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000