Query, help with where clause to reduce resultset

I need some help with the @.LanguageId part of this query to exclude/include
rows to be returned.
ctbl_content_rel can contain rows with different language ids, e.g 'dk' and
'nn'.
If rows with the specified @.LanguageId exists, then I want only those rows,
otherwise I want only the 'nn' rows (a fallback mechanism).
Now the query returns all the rows for both the specified @.LanguageId and
those with @.LanguageId = 'nn'. Any ideas?
SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
FROM ctbl_content_rel CONTENTREL
JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
WHERE CONTENT.pk_content = @.ContentId
AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid = 'NN')
/tedHow about this?
SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
FROM ctbl_content_rel CONTENTREL
JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
WHERE CONTENT.pk_content = @.ContentId
AND CONTENTREL.languageId = CASE WHEN @.LanguageId = '' THEN 'NN' ELSE
@.LanguageId END
HTH,
Pierre
/"\ ASCII Ribbon Campaign
\ /
X Against HTML
/ \ in e-mail & news|||I'm afraid it will not solve the problem. @.LanguageId will always be
something as input parameter. The clue is that I want the 'nn' rows if the
specified 'dk' rows does not exist in the database.
/ted :)
"Pierre Albisser" <pierre.no_spam@.albisser.de> skrev i melding
news:%23079C5GXFHA.4032@.tk2msftngp13.phx.gbl...
> How about this?
> SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> FROM ctbl_content_rel CONTENTREL
> JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
> WHERE CONTENT.pk_content = @.ContentId
> AND CONTENTREL.languageId = CASE WHEN @.LanguageId = '' THEN 'NN' ELSE
> @.LanguageId END
> --
> HTH,
> Pierre
> /"\ ASCII Ribbon Campaign
> \ /
> X Against HTML
> / \ in e-mail & news|||Try,
if exists(select * from ctbl_content_rel CONTENTREL JOIN ctbl_content
CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content WHERE
CONTENT.pk_content = @.ContentId AND (CONTENTREL.languageId = @.LanguageId))
select distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
from ctbl_content_rel CONTENTREL JOIN ctbl_content CONTENT ON
CONTENTREL.fk_content = CONTENT.pk_content WHERE CONTENT.pk_content =
@.ContentId AND (CONTENTREL.languageId = @.LanguageId)
else
select distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
from ctbl_content_rel CONTENTREL JOIN ctbl_content CONTENT ON
CONTENTREL.fk_content = CONTENT.pk_content WHERE CONTENT.pk_content =
@.ContentId AND (CONTENTREL.languageid = 'NN')
AMB
"ted" wrote:

> I need some help with the @.LanguageId part of this query to exclude/includ
e
> rows to be returned.
> ctbl_content_rel can contain rows with different language ids, e.g 'dk' an
d
> 'nn'.
> If rows with the specified @.LanguageId exists, then I want only those rows
,
> otherwise I want only the 'nn' rows (a fallback mechanism).
> Now the query returns all the rows for both the specified @.LanguageId and
> those with @.LanguageId = 'nn'. Any ideas?
> SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> FROM ctbl_content_rel CONTENTREL
> JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
> WHERE CONTENT.pk_content = @.ContentId
> AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid = 'NN')
> /ted
>
>|||Hi Ted
Probable you can try this way
SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
FROM ctbl_content_rel CONTENTREL
JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
WHERE CONTENT.pk_content = @.ContentId
AND CONTENTREL.languageId = ISNULL(@.LanguageId,'NN')
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"ted" wrote:

> I need some help with the @.LanguageId part of this query to exclude/includ
e
> rows to be returned.
> ctbl_content_rel can contain rows with different language ids, e.g 'dk' an
d
> 'nn'.
> If rows with the specified @.LanguageId exists, then I want only those rows
,
> otherwise I want only the 'nn' rows (a fallback mechanism).
> Now the query returns all the rows for both the specified @.LanguageId and
> those with @.LanguageId = 'nn'. Any ideas?
> SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> FROM ctbl_content_rel CONTENTREL
> JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
> WHERE CONTENT.pk_content = @.ContentId
> AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid = 'NN')
> /ted
>
>|||Thanks :) It seems to be a way to go.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> skrev i melding
news:2E7D1FD3-54AC-4A40-87E4-8EA9EEFF5F6F@.microsoft.com...
> Try,
> if exists(select * from ctbl_content_rel CONTENTREL JOIN ctbl_content
> CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content WHERE
> CONTENT.pk_content = @.ContentId AND (CONTENTREL.languageId = @.LanguageId))
> select distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> from ctbl_content_rel CONTENTREL JOIN ctbl_content CONTENT ON
> CONTENTREL.fk_content = CONTENT.pk_content WHERE CONTENT.pk_content =
> @.ContentId AND (CONTENTREL.languageId = @.LanguageId)
> else
> select distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> from ctbl_content_rel CONTENTREL JOIN ctbl_content CONTENT ON
> CONTENTREL.fk_content = CONTENT.pk_content WHERE CONTENT.pk_content =
> @.ContentId AND (CONTENTREL.languageid = 'NN')
>
> AMB
> "ted" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Yoiu might also want to start following proper data modeling
conventions and stop using prefixes that tell us HOW something is used
in one table, and come with names that tell us what something is in the
whole data model. A prefix that tells us something is a table in a
language that hsa only one data strucutre is absurd.
See ISO-11179 for the standards. Can I assume that you meant to use
the ISO language codes and not a identifier?|||On Thu, 19 May 2005 14:19:58 +0200, ted wrote:

>I need some help with the @.LanguageId part of this query to exclude/include
>rows to be returned.
>ctbl_content_rel can contain rows with different language ids, e.g 'dk' and
>'nn'.
>If rows with the specified @.LanguageId exists, then I want only those rows,
>otherwise I want only the 'nn' rows (a fallback mechanism).
>Now the query returns all the rows for both the specified @.LanguageId and
>those with @.LanguageId = 'nn'. Any ideas?
>SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
>FROM ctbl_content_rel CONTENTREL
>JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
>WHERE CONTENT.pk_content = @.ContentId
>AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid = 'NN')
>/ted
>
Hi ted,
If you're not afraid of using proprietary SQL that won't port to other
databases, you can use this:
SELECT TOP 1 CONTENTREL.fk_filegroup, CONTENTREL.languageid
FROM ctbl_content_rel CONTENTREL
JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
WHERE CONTENT.pk_content = @.ContentId
AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid =
'NN')
ORDER BY CASE WHEN CONTENTREL.languageid = 'NN' THEN 2 ELSE 1 END
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql