Hi Mats,

Thank you for the update. If you have any questions or concerns, please
feel free to let me know.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi Mats,

Thank you for your posting!

I have noticed that you have posted the same questions in this newsgroup.
And there is a customer answered your problem there. I agree with him and I
will post the answer here:

From: Roy Harvey <roy_harvey@snet.net>
Subject: Re: Need help with a query
Date: Thu, 15 Jun 2006 18:05:12 -0400
Newsgroups: microsoft.public.sqlserver.programming

SELECT B.*, L.*,
COALESCE(L.CultureID,
(select CultureID from CultureTable
where IsDefault = 1))
FROM BaseTable as B
LEFT OUTER
JOIN LocalizedTable as L
ON B.Id = L.Id
AND L.CultureID = @CultureID

A column name of Id is pretty horrible, by the way. It should be
qualified by something, BaseId perhaps.

Roy Harvey
Beacon Falls, CT

Hope this will be helpful!

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi again Wei,
I know this is kind of late to write a reply to this thread but I just
wanted to say I'm impressed with the kind of support that is offered
here in this thread. I just expected to get a hint to as what I should
do, not a complete query, a very pleasant surprise indeed.

I thought that as a practice for future postings I would paste my DDL
and the solution I found to my problem, I actually found the solution in
another post.

The trick was to use NOT EXISTS in the where clause, like this:
WHERE A.CultureID = @CultureID OR
(
A.CultureID = (SELECT CultureID from cms_Language WHERE IsDefault = 1)
AND NOT EXISTS
(
SELECT * FROM vw_cms_WSCategory as B WHERE B.CultureID =
@CultureID AND A.CategoryID = B.CategoryID
)
)

You can try run the sql snippet to create the tables and populate them
and finally run execute the sproc with the command below to see the result.

The post became quite lengthy but I can really agree that there is not
much room for misunderstanding when being this thorough.

Thanks for the replies and I look forward to making posts to this groups
in the future.

Best regards,
Mats Lycken

btw, I've already changed the CultureID nvarchar(10) to a char(5)... ;)



/* Command to execute begins (Use this to execute the sproc) */

DECLARE @RC int
DECLARE @CultureID nvarchar(10)

SET @CultureID = 'en-us'

EXECUTE @RC = [vw_cms_WSCategory_Sel]
@CultureID

/* Command to execute ends */

/* ------ SQL snippet begins, copy from here... */

/*
*
* Here is the DDL for my two tables:
*
*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cms_WSCategory](
[CategoryID] [uniqueidentifier] NOT NULL,
[Published] [bit] NOT NULL,
[CreatedBy] [uniqueidentifier] NOT NULL,
[Created] [datetime] NOT NULL,
[Parent] [uniqueidentifier] NULL,
[SortOrder] [int] NOT NULL CONSTRAINT [DF_cms_WSCategory_SortOrder]
DEFAULT ((0)),
[OverrideLink] [nvarchar](256) COLLATE Finnish_Swedish_CI_AS NULL,
CONSTRAINT [PK_cms_WSCategory] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[cms_WSCategoryLocalized] Script Date:
06/21/2006 13:36:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cms_WSCategoryLocalized](
[CategoryID] [uniqueidentifier] NOT NULL,
[CultureID] [nvarchar](10) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[DisplayName] [nvarchar](64) COLLATE Finnish_Swedish_CI_AS NULL,
[LastModifiedBy] [uniqueidentifier] NOT NULL,
[LastModified] [datetime] NOT NULL,
[DisplayHTML] [nvarchar](1024) COLLATE Finnish_Swedish_CI_AS NULL,
CONSTRAINT [PK_cms_WSCategoryLocalized] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC,
[CultureID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[cms_WSCategoryLocalized] WITH CHECK ADD CONSTRAINT
[FK_cms_WSCategoryLocalized_cms_WSCategory] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[cms_WSCategory] ([CategoryID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[cms_WSCategoryLocalized] CHECK CONSTRAINT
[FK_cms_WSCategoryLocalized_cms_WSCategory]
GO
/*
*
* Create the language table
*
*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cms_Language](
[DisplayName] [nvarchar](50) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[CultureID] [nvarchar](10) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[IsDefault] [bit] NOT NULL CONSTRAINT [DF_cms_Language_IsDefault]
DEFAULT ((0))
) ON [PRIMARY]
GO
/*
*
* Here are some inserts for the base table:
*
*/
INSERT INTO [cms_WSCategory]
VALUES('3B217318-2C34-4536-9239-2F7157AAFB39',1,'00000000-0000-0000-0000-000000000000','Dec
20 2005 11:15:39:987AM','00000000-0000-0000-0000-000000000000',0,NULL)
GO
INSERT INTO [cms_WSCategory]
VALUES('41D0DFD3-BEF2-4215-BF3A-5387FEB9774D',1,'00000000-0000-0000-0000-000000000000','Dec
20 2005 11:14:17:440AM','00000000-0000-0000-0000-000000000000',0,'skiplink')
GO
/*
*
* Here are some inserts for the localized table:
*
*/
INSERT INTO [cms_WSCategoryLocalized]
VALUES('3B217318-2C34-4536-9239-2F7157AAFB39','nn-no','Oppbygget
gulv','00000000-0000-0000-0000-000000000000','Dec 20 2005
11:15:39:987AM',NULL)
GO
INSERT INTO [cms_WSCategoryLocalized]
VALUES('41D0DFD3-BEF2-4215-BF3A-5387FEB9774D','en-us','Carpets','00000000-0000-0000-0000-000000000000','Dec
20 2005 11:14:17:000AM','')
GO
INSERT INTO [cms_WSCategoryLocalized]
VALUES('41D0DFD3-BEF2-4215-BF3A-5387FEB9774D','nn-no','Tepper','00000000-0000-0000-0000-000000000000','Dec
20 2005 11:14:17:440AM','<span style="font-weigth: bold; color:
Red;">Tepper</span>')
GO
/*
*
* Here are some inserts for the language table:
*
*/
INSERT INTO [cms_Language] VALUES('English','en-us',0)
GO
INSERT INTO [cms_Language] VALUES('Norsk','nn-no',1)
GO
/*
*
* Create a view from the two tables
*
*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_cms_WSCategory]
AS
SELECT dbo.cms_WSCategory.CategoryID, dbo.cms_WSCategory.Published,
dbo.cms_WSCategory.CreatedBy, dbo.cms_WSCategory.Created,
dbo.cms_WSCategoryLocalized.CultureID,
dbo.cms_WSCategoryLocalized.DisplayName,
dbo.cms_WSCategoryLocalized.LastModifiedBy,
dbo.cms_WSCategoryLocalized.LastModified,
dbo.cms_WSCategory.Parent, dbo.cms_WSCategory.SortOrder,
dbo.cms_WSCategoryLocalized.DisplayHTML,
dbo.cms_WSCategory.OverrideLink
FROM dbo.cms_WSCategory INNER JOIN
dbo.cms_WSCategoryLocalized ON
dbo.cms_WSCategory.CategoryID = dbo.cms_WSCategoryLocalized.CategoryID

GO
/*
*
* Here is the stored procedure that solved my issue:
*
*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vw_cms_WSCategory_Sel]
(
@CultureID nvarchar(10)
)
AS



SELECT *
FROM vw_cms_WSCategory as A
WHERE A.CultureID = @CultureID OR
(
A.CultureID = (SELECT CultureID from cms_Language WHERE IsDefault = 1)
AND NOT EXISTS
(
SELECT * FROM vw_cms_WSCategory as B WHERE B.CultureID =
@CultureID AND A.CategoryID = B.CategoryID
)
)
ORDER BY SortOrder ASC, DisplayName ASC
RETURN
GO

/* ------ SQL snippet ends, ...to here. */

[quoted text, click to view]