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 2000 Forums

Transact-SQL (2000)

Join whether exists or not.

Author

Topic

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2007-09-02 : 14:31:38

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:34:48

Cross posting http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88789

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources