sql query help

[Hammy Hammy]

Hi all, I have an invoice table query that returns 10 records. ie. there
are 10 invoices.

When I try to join a subjects table to retrieve the subject name
associated with an invoice it returns 11 records.

I know this is because for each invoice, there might be multiple
subjects. So when I join the subject table to get the subject's name, it
will add an extra record.

How can I get the query to return only the 10 records, but for that
single record that has 2 subjects, to show both subjects in the same
field for that record?

This query gets 11 records:
*************** *************** *********
select invoiceid,
subject.name,
files.file_numb er
from invoices
inner join files on files.file_numb er = invoices.file_n umber
inner join subject on subject.file_nu mber = files.file_numb er
where invoices.invoic eID between 3173 and 3183
order by invoiceid
*************** *************** ************

So instead of having the results look like this:

invoiceID name file_number
3173 jon 22222
3173 jane 22222

I would like:

invoiceID name file_number
3173 jon and jane 22222

Thanks.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Re: sql query help

[posted and mailed, please reply in news]

Hammy Hammy (chris@thehams. ca) writes:[color=blue]
> When I try to join a subjects table to retrieve the subject name
> associated with an invoice it returns 11 records.
>
> I know this is because for each invoice, there might be multiple
> subjects. So when I join the subject table to get the subject's name, it
> will add an extra record.
>
> How can I get the query to return only the 10 records, but for that
> single record that has 2 subjects, to show both subjects in the same
> field for that record?[/color]

As long as you only have two subjects, this will work:

select i.invoiceid, CASE WHEN COUNT(*) = 1
THEN MIN(s.name)
ELSE MIN(st.name) + ' and ' + MAX(s.name)
END, f.file_number
from invoices i
join files f on f.file_number = i.file_number
join subject on s.file_number = f.file_number
where i.invoiceID between 3173 and 3183
group by i.invoiceid, f.file_number
order by i.invoiceid

But this breaks completely, there are three suhjects, and you want
to see them all. In such case you need to use iterative processing
and this is no fun at all. It might be better to do this on client
level, as client languages are more apt to this kind of thing.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp