problem query

[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!