Challenging query

microsoft.public.sqlserver.programming

8 Sep 2006 11:29 PM

Gordon

I've got a challenge -- I have a one-to-many relationship - files to
filecategories -- and but only want one row back per file, not matter how
many categories it fits into. Distinct won't do it, because I have text
columns in the query, and casting them as varchars doesn't help - SQL 2000
still balks (and I can't change the SQL version right now). So I thought I'd
use an inline select:

select f.fileid, thumbnail, articledate,
blurb, thumbcaption
from files f inner join filedetails fd on f.fileid = fd.fileid
inner join (select top 1 detailid from filecategories
where keyissueid = 7 and detailid = fd.detailid) as fc
on fd.detailid = fc.detailid
Order By f.fileid

That way, I'd only get back one row from filecategories for the join! But,
apparently you can't reference the columns in the other tables. So, I wrote a
Table-UDF, but that, too, won't accept column values from other tables as
input parameters. Right now, I'm looping through a lot of duplicates
client-side and logging dupes in a dictionary object so I don't list a file
twice (e.g. a file could be listed under keyissueid of 7 and then again with
a keyissueid of 7 and subkeyissueid of 28. When I just want all files
categorized with a keyissue of 7, I don't want to see the file returned
twice.)

Thanks for insights anyone may have.

9 Sep 2006 6:40 AM

Anith Sen

For such problems, always post your table DDLs, sample data & expected
results. For details, refer to www.aspfaq.com/5006

In your case, based on your narrative, you should be able to write a
correlated subquery like:

SELECT *             -- req. columns
FROM files f
JOIN filedetails fd
    ON f.fileid = fd.fileid
JOIN ( SELECT keyissueid, MAX( detailid )
           FROM filecategories
          GROUP BY keyissueid ) fc ( keyissueid, detailid )
    ON fd.detailid = fc.detailid
WHERE fc.keyissueid = 7 ;

This is based on various assumptions on the relationships represented with
tables filecategories and filedetails tables which may or may not be
accurate. If you still have issues, consider providing the info requested in
the first para above.

--
Anith

11 Sep 2006 2:16 PM

Jim Underwood

The code you have below is not far off, if I understand what you are trying
to do.

As Anith said, supplying DDL is important, and some sample data would allow
us to test the solution.

However, here are the POTENTIAL problems I see with yor SQL below.

1. you did not qualify all of your collumns with the table alias, so we have
no idea what tables these are coming from, nor will SQL Server if the same
columns exist in multiple tables.

2. You have a coorelated subquery which already includes a join, but you
specified the join columns anyway. This is not wrong, but is redundant.
You don't need the line "on fd.detailid = fc.detailid". If it makes the
code easier for you to follow, then by all means, leave it in.

3. You are not selecting any columns other than detailID from your subquery.
You have to select any column that you want to access in the outer query.
So, if thumbcaption is from file categories, then you need to select it in
the subquery. If you do not need any other columns from filecategories,
then an exist MAY work better than a join.

4. Without an order by clause in your subquery the TOP will select a more or
less random row from filecategories. You might not care which row you get
back as long as you get back one, but this is worth mentioning anyway.
Depending on how SQL Server decides to process the query, you could get back
different TOP 1 rows from this table.

Show quote

"Gordon" <Gor***@discussions.microsoft.com> wrote in message
news:2962175B-3379-4DFF-83D3-9B2A75C8F715@microsoft.com...
> I've got a challenge -- I have a one-to-many relationship - files to
> filecategories -- and but only want one row back per file, not matter how
> many categories it fits into. Distinct won't do it, because I have text
> columns in the query, and casting them as varchars doesn't help - SQL 2000
> still balks (and I can't change the SQL version right now). So I thought
I'd
> use an inline select:
>
> select f.fileid, thumbnail, articledate,
> blurb, thumbcaption
> from files f inner join filedetails fd on f.fileid = fd.fileid
> inner join (select top 1 detailid from filecategories
> where keyissueid = 7 and detailid = fd.detailid) as fc
> on fd.detailid = fc.detailid
> Order By f.fileid
>
> That way, I'd only get back one row from filecategories for the join! But,
> apparently you can't reference the columns in the other tables. So, I
wrote a
> Table-UDF, but that, too, won't accept column values from other tables as
> input parameters. Right now, I'm looping through a lot of duplicates
> client-side and logging dupes in a dictionary object so I don't list a
file
> twice (e.g. a file could be listed under keyissueid of 7 and then again
with
> a keyissueid of 7 and subkeyissueid of 28. When I just want all files
> categorized with a keyissue of 7, I don't want to see the file returned
> twice.)
>
> Thanks for insights anyone may have.

11 Sep 2006 4:27 PM

Gordon

Thanks Anith and Jim,

The key mistakes were -- top 1 instead of distinct -- and somehow adding the
on fd.detailid = fc.detailid in the correlated query when it was not
necessary. Your posts got me thinking and going in the right direction.

I might add that, because I never know whether I will get one or all four
parameters, I was able to make use of case statements in the query to replace
if statements in a stored procedure. I'd be happy to attach if anyone is
interested. It gets busy, but it reduces four if statements to one sql
statement.

Gordon
Show quote

"Jim Underwood" wrote:

> The code you have below is not far off, if I understand what you are trying
> to do.
>
> As Anith said, supplying DDL is important, and some sample data would allow
> us to test the solution.
>
> However, here are the POTENTIAL problems I see with yor SQL below.
>
> 1. you did not qualify all of your collumns with the table alias, so we have
> no idea what tables these are coming from, nor will SQL Server if the same
> columns exist in multiple tables.
>
> 2. You have a coorelated subquery which already includes a join, but you
> specified the join columns anyway. This is not wrong, but is redundant.
> You don't need the line "on fd.detailid = fc.detailid". If it makes the
> code easier for you to follow, then by all means, leave it in.
>
> 3. You are not selecting any columns other than detailID from your subquery.
> You have to select any column that you want to access in the outer query.
> So, if thumbcaption is from file categories, then you need to select it in
> the subquery. If you do not need any other columns from filecategories,
> then an exist MAY work better than a join.
>
> 4. Without an order by clause in your subquery the TOP will select a more or
> less random row from filecategories. You might not care which row you get
> back as long as you get back one, but this is worth mentioning anyway.
> Depending on how SQL Server decides to process the query, you could get back
> different TOP 1 rows from this table.
>
> "Gordon" <Gor***@discussions.microsoft.com> wrote in message
> news:2962175B-3379-4DFF-83D3-9B2A75C8F715@microsoft.com...
> > I've got a challenge -- I have a one-to-many relationship - files to
> > filecategories -- and but only want one row back per file, not matter how
> > many categories it fits into. Distinct won't do it, because I have text
> > columns in the query, and casting them as varchars doesn't help - SQL 2000
> > still balks (and I can't change the SQL version right now). So I thought
> I'd
> > use an inline select:
> >
> > select f.fileid, thumbnail, articledate,
> > blurb, thumbcaption
> > from files f inner join filedetails fd on f.fileid = fd.fileid
> > inner join (select top 1 detailid from filecategories
> > where keyissueid = 7 and detailid = fd.detailid) as fc
> > on fd.detailid = fc.detailid
> > Order By f.fileid
> >
> > That way, I'd only get back one row from filecategories for the join! But,
> > apparently you can't reference the columns in the other tables. So, I
> wrote a
> > Table-UDF, but that, too, won't accept column values from other tables as
> > input parameters. Right now, I'm looping through a lot of duplicates
> > client-side and logging dupes in a dictionary object so I don't list a
> file
> > twice (e.g. a file could be listed under keyissueid of 7 and then again
> with
> > a keyissueid of 7 and subkeyissueid of 28. When I just want all files
> > categorized with a keyissue of 7, I don't want to see the file returned
> > twice.)
> >
> > Thanks for insights anyone may have.
>
>
>