•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 455,969 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,760 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser: Programming Forums
Views: 4557 | Replies: 5 | Solved
 |
| |
•
•
Join Date: Nov 2007
Posts: 1
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
get only ONE row with different fields from same table Hi,
I'm a bit stuck here.
I have a table called STM_COMM and it contains following fields: id, member_id, email_addr, tel_nr, fax_nr
A member can have multiple email addresses, telephone numbers and faxnumbers. so we could have something like this:
id member_id email_addr tel_nr fax_nr
----------------------------------------------------------------
1 5 info@test.com
2 5 555687 568774
3 66 test@go.nl 65897854
4 5 another@one.com
What I would like to have is only ONE (1) row PER member with only the FIRST (not max!) entered known data. In the above case, I'd like to have this as result:
member_id email_addr tel_nr fax_nr
----------------------------------------------------
5 info@test.com 555687 568774
66 test@go.nl 65897854
I've tried lots of things (max, inner select ...) but I can't seem to figure it out.
Thank you for the help !
Christophe
PS: I'm working on a sql server 2005
I'm a bit stuck here.
I have a table called STM_COMM and it contains following fields: id, member_id, email_addr, tel_nr, fax_nr
A member can have multiple email addresses, telephone numbers and faxnumbers. so we could have something like this:
id member_id email_addr tel_nr fax_nr
----------------------------------------------------------------
1 5 info@test.com
2 5 555687 568774
3 66 test@go.nl 65897854
4 5 another@one.com
What I would like to have is only ONE (1) row PER member with only the FIRST (not max!) entered known data. In the above case, I'd like to have this as result:
member_id email_addr tel_nr fax_nr
----------------------------------------------------
5 info@test.com 555687 568774
66 test@go.nl 65897854
I've tried lots of things (max, inner select ...) but I can't seem to figure it out.
Thank you for the help !
Christophe
PS: I'm working on a sql server 2005
•
•
Join Date: Feb 2008
Posts: 3
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Did you ever find a solution? I've got the same problem and can't find the way to do it. It seems a reasonable thing to want to do, so I would have thought someone would know!
Running the same query but then discarding all but the first occurrence of each row from the 'master' table would work but I don't know how to do it in SQL. There might be other methods.
Running the same query but then discarding all but the first occurrence of each row from the 'master' table would work but I don't know how to do it in SQL. There might be other methods.
•
•
Join Date: Nov 2007
Location: � Jogja �
Posts: 2,590
Reputation: 
Rep Power: 11
Solved Threads: 236
if i did not wrong to understand :
select member_id, email_addr, tel_nr, fax_nr from STM_COMM where member_id = 5
select member_id, email_addr, tel_nr, fax_nr from STM_COMM where member_id = 5
Last edited by Jx_Man : Feb 7th, 2008 at 10:56 am.
Never tried = Never Know
So, Please do something before post your thread.
* PM Asking will be ignored *
So, Please do something before post your thread.
* PM Asking will be ignored *
•
•
Join Date: Feb 2008
Posts: 3
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
No, the problem is if you have something like
select staff.name, review.date, promotion.date
from
staff
left join review on review.staff_id=staff.id
left join promotion on promotion.staff_id=staff.id
The problem is when you have more than one review record, and more than one promotion record, for a staff member, you get multiple rows returned for each staff record. If a staff member has 2 review and 3 promotions you get 6 records for that person. I only want a single row for each person, with the most recent entry from each of the linked tables.
select staff.name, review.date, promotion.date
from
staff
left join review on review.staff_id=staff.id
left join promotion on promotion.staff_id=staff.id
The problem is when you have more than one review record, and more than one promotion record, for a staff member, you get multiple rows returned for each staff record. If a staff member has 2 review and 3 promotions you get 6 records for that person. I only want a single row for each person, with the most recent entry from each of the linked tables.
•
•
Join Date: Nov 2007
Location: Bangalore, India
Posts: 3,098
Reputation:
Rep Power: 8
Solved Threads: 240
mysql Syntax (Toggle Plain Text)
Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
*PM asking for help will be ignored*
*PM asking for help will be ignored*
•
•
Join Date: Feb 2008
Posts: 3
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Unfortunately not. You get something like "Column 'staff.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause". This is MS SQL 2000 I'm using.
This seems to work but is long winded especially with 7 or more tables to join. Is there a better way?
This seems to work but is long winded especially with 7 or more tables to join. Is there a better way?
SQL Syntax (Toggle Plain Text)
|
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Hybrid Mode
- How to change the color of selected row of an HTML table without using CSS or Javascr (VB.NET)
- MYSQL: update all fields in a table (increase value by one) (PHP)
- how to get the row id of the clicked row in a HTML table using c# (C#)
- How to link a data row to datetimepicker (VB.NET)
- How to select the Last row from a table..using Mysql (MySQL)
- How to multiply two value from two fields (MS SQL)
- How to create a Temporary Table (MS SQL)
- Print 5 records per table row (see script) (Perl)
Other Threads in the MS SQL Forum
- Previous Thread: How do I ban ip addresses using MsSQL script?
- Next Thread: select
