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

query to use

Hi
i have 2 tables. Table 2 can contain some values for each record in
table1. may vary for the no:of records in table2 for each record in
table1
table1
=====
id name
1 Arun
2 Hari
Table2
=====
id table1.id some_field
1 1 x
2 1 y
3 1 z
4 2 d
I want to get a display like the following
id name 1 2 3
1 Arun x y z
or
id name 1
1 Hari d
What query I have to use
?
Hi
--SQL Server 2000
create table #table1 (id int,name varchar(50))
insert into #table1 values(1,'Arun')
insert into #table1 values(2,'Hari')
create table #table2 (id int,anotherid int, some_field varchar(50))
insert into #table2 values(1,1,'x')
insert into #table2 values(2,1,'y')
insert into #table2 values(3,1,'z')
insert into #table2 values(4,2,'d')
select * from #table1
select * from #table2
select name,max(case when rn=1 then some_field end) as '1',
max(case when rn=2 then some_field end) as '2',
max(case when rn=3 then some_field end) as '3'
from
(
select t2.anotherid,t2.some_field,count(*)rn from #table2,#table2 t2
where t2.anotherid=#table2.anotherid and t2.id<=#table2.id
group by t2.anotherid,t2.some_field
) as d join #table1 on d.anotherid=#table1.id
group by name
--SQL Server 2005
select * from
(
select t1.id ,name,anotherid,some_field,ROW_NUMBER() OVER(
PARTITION BY anotherid
ORDER BY some_field) AS pos
from #table1 AS t1
join #table2 AS t2
ON t1.id = t2.anotherid
) as der
pivot
(
max(some_field)
FOR pos IN([1], [2], [3], [4])
) AS PVT
<arunonw3@.gmail.com> wrote in message
news:1176193651.677044.91870@.l77g2000hsb.googlegro ups.com...
> Hi
> i have 2 tables. Table 2 can contain some values for each record in
> table1. may vary for the no:of records in table2 for each record in
> table1
> table1
> =====
> id name
> 1 Arun
> 2 Hari
>
> Table2
> =====
> id table1.id some_field
> 1 1 x
> 2 1 y
> 3 1 z
> 4 2 d
> I want to get a display like the following
>
> id name 1 2 3
> 1 Arun x y z
> or
> id name 1
> 1 Hari d
> What query I have to use
> ?
>
|||Thank you very much for sending me such a useful answer
|||If you dont mind can you please explain the last 2 queries

query to use

Hi
i have 2 tables. Table 2 can contain some values for each record in
table1. may vary for the no:of records in table2 for each record in
table1
table1
===== id name
1 Arun
2 Hari
Table2
===== id table1.id some_field
1 1 x
2 1 y
3 1 z
4 2 d
I want to get a display like the following
id name 1 2 3
1 Arun x y z
or
id name 1
1 Hari d
What query I have to use
?Hi
--SQL Server 2000
create table #table1 (id int,name varchar(50))
insert into #table1 values(1,'Arun')
insert into #table1 values(2,'Hari')
create table #table2 (id int,anotherid int, some_field varchar(50))
insert into #table2 values(1,1,'x')
insert into #table2 values(2,1,'y')
insert into #table2 values(3,1,'z')
insert into #table2 values(4,2,'d')
select * from #table1
select * from #table2
select name,max(case when rn=1 then some_field end) as '1',
max(case when rn=2 then some_field end) as '2',
max(case when rn=3 then some_field end) as '3'
from
(
select t2.anotherid,t2.some_field,count(*)rn from #table2,#table2 t2
where t2.anotherid=#table2.anotherid and t2.id<=#table2.id
group by t2.anotherid,t2.some_field
) as d join #table1 on d.anotherid=#table1.id
group by name
--SQL Server 2005
select * from
(
select t1.id ,name,anotherid,some_field,ROW_NUMBER() OVER(
PARTITION BY anotherid
ORDER BY some_field) AS pos
from #table1 AS t1
join #table2 AS t2
ON t1.id = t2.anotherid
) as der
pivot
(
max(some_field)
FOR pos IN([1], [2], [3], [4])
) AS PVT
<arunonw3@.gmail.com> wrote in message
news:1176193651.677044.91870@.l77g2000hsb.googlegroups.com...
> Hi
> i have 2 tables. Table 2 can contain some values for each record in
> table1. may vary for the no:of records in table2 for each record in
> table1
> table1
> =====> id name
> 1 Arun
> 2 Hari
>
> Table2
> =====> id table1.id some_field
> 1 1 x
> 2 1 y
> 3 1 z
> 4 2 d
> I want to get a display like the following
>
> id name 1 2 3
> 1 Arun x y z
> or
> id name 1
> 1 Hari d
> What query I have to use
> ?
>|||Thank you very much for sending me such a useful answer|||If you dont mind can you please explain the last 2 queries

query to use

Hi
i have 2 tables. Table 2 can contain some values for each record in
table1. may vary for the no:of records in table2 for each record in
table1
table1
=====
id name
1 Arun
2 Hari
Table2
=====
id table1.id some_field
1 1 x
2 1 y
3 1 z
4 2 d
I want to get a display like the following
id name 1 2 3
1 Arun x y z
or
id name 1
1 Hari d
What query I have to use
?Hi
--SQL Server 2000
create table #table1 (id int,name varchar(50))
insert into #table1 values(1,'Arun')
insert into #table1 values(2,'Hari')
create table #table2 (id int,anotherid int, some_field varchar(50))
insert into #table2 values(1,1,'x')
insert into #table2 values(2,1,'y')
insert into #table2 values(3,1,'z')
insert into #table2 values(4,2,'d')
select * from #table1
select * from #table2
select name,max(case when rn=1 then some_field end) as '1',
max(case when rn=2 then some_field end) as '2',
max(case when rn=3 then some_field end) as '3'
from
(
select t2.anotherid,t2.some_field,count(*)rn from #table2,#table2 t2
where t2.anotherid=#table2.anotherid and t2.id<=#table2.id
group by t2.anotherid,t2.some_field
) as d join #table1 on d.anotherid=#table1.id
group by name
--SQL Server 2005
select * from
(
select t1.id ,name,anotherid,some_field,ROW_NUMBER() OVER(
PARTITION BY anotherid
ORDER BY some_field) AS pos
from #table1 AS t1
join #table2 AS t2
ON t1.id = t2.anotherid
) as der
pivot
(
max(some_field)
FOR pos IN([1], [2], [3], [4])
) AS PVT
<arunonw3@.gmail.com> wrote in message
news:1176193651.677044.91870@.l77g2000hsb.googlegroups.com...
> Hi
> i have 2 tables. Table 2 can contain some values for each record in
> table1. may vary for the no:of records in table2 for each record in
> table1
> table1
> =====
> id name
> 1 Arun
> 2 Hari
>
> Table2
> =====
> id table1.id some_field
> 1 1 x
> 2 1 y
> 3 1 z
> 4 2 d
> I want to get a display like the following
>
> id name 1 2 3
> 1 Arun x y z
> or
> id name 1
> 1 Hari d
> What query I have to use
> ?
>|||Thank you very much for sending me such a useful answer|||If you dont mind can you please explain the last 2 queriessql

Query to display a field based on a parameter

I have a client table, with 8 fields, all of which contain a phone number.
Bad design, I know, but let's not get into that here. My problem now is
that in addition to those fields, we have a "Preferred" field, which simply
names the field of the preferred phone number. So we have fields like
HomePhone, BusinessPhone, Fax, and the Preferred field says "HomePhone"
How can I create a query so that the record displays only that field that is
preferred? I hope that makes sense.
For instance: John Doe, Home Phone 123-4567, Business Phone 765-4321,
Preferred "Home Phone", I want to the query to only display the name, and
home phone.
For Jane Doe, Home Phone 123-4567, Business Phone 765-4321, Preferred
"Business Phone", I want to the query to only display the name, and business
phone.
All of these records are displayed in a datagrid.
Thanks for your help.SELECT CASE Preferred
WHEN 'Home Phone' THEN HomePhone
WHEN 'Work Phone' THEN WorkPhone
..
WHEN 'Yet Another Phone' THEN YetAnotherPhone
End as PreferredPhone
FROM LotsOfPhones
Roy Harvey
Beacon Falls, CT
On Fri, 7 Apr 2006 16:29:47 -0600, "KatMagic" <SSKatMagic@.yahoo.com>
wrote:

>I have a client table, with 8 fields, all of which contain a phone number.
>Bad design, I know, but let's not get into that here. My problem now is
>that in addition to those fields, we have a "Preferred" field, which simply
>names the field of the preferred phone number. So we have fields like
>HomePhone, BusinessPhone, Fax, and the Preferred field says "HomePhone"
>How can I create a query so that the record displays only that field that i
s
>preferred? I hope that makes sense.
>For instance: John Doe, Home Phone 123-4567, Business Phone 765-4321,
>Preferred "Home Phone", I want to the query to only display the name, and
>home phone.
>For Jane Doe, Home Phone 123-4567, Business Phone 765-4321, Preferred
>"Business Phone", I want to the query to only display the name, and busines
s
>phone.
>All of these records are displayed in a datagrid.
>Thanks for your help.
>