473,326 Members | 2,081 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

sql query help

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_number
from invoices
inner join files on files.file_number = invoices.file_number
inner join subject on subject.file_number = files.file_number
where invoices.invoiceID 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!
Jul 20 '05 #1
1 1835
[posted and mailed, please reply in news]

Hammy Hammy (ch***@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
7
by: Simon Bailey | last post by:
How do you created a query in VB? I have a button on a form that signifies a certain computer in a computer suite. On clicking on this button i would like to create a query searching for all...
4
by: d.p. | last post by:
Hi all, I'm using MS Access 2003. Bare with me on this description....here's the situation: Imagine insurance, and working out premiums for different insured properties. The rates for calculating...
4
by: Alan Lane | last post by:
Hello world: I'm including both code and examples of query output. I appologize if that makes this message longer than it should be. Anyway, I need to change the query below into a pivot table...
36
by: Liam.M | last post by:
hey guys, I have one last problem to fix, and then my database is essentially done...I would therefore very much appreciate any assistance anyone would be able to provide me with. Currently I...
5
by: elitecodex | last post by:
Hey everyone. I have this query select * from `TableName` where `SomeIDField` 0 I can open a mysql command prompt and execute this command with no issues. However, Im trying to issue the...
10
by: aaronrm | last post by:
I have a real simple cross-tab query that I am trying to sum on as the action but I am getting the "data type mismatch criteria expression" error. About three queries up the food chain from this...
11
by: funky | last post by:
hello, I've got a big problem ad i'm not able to resolve it. We have a server running oracle 10g version 10.1.0. We usually use access as front end and connect database tables for data extraction....
4
by: Doris | last post by:
It does not look like my message is posting....if this is a 2nd or 3rd message, please forgive me as I really don't know how this site works. I want to apologize ahead of time for being a novice...
6
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.