[posted and mailed, please reply in news]

Hammy Hammy (chris@thehams.ca) writes:
> 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?

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/techinf...2000/books.asp