get only ONE row with different fields from same table

[MedievalKnight]

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 [e-mail]
2 5 555687 568774
3 66 [e-mail] 65897854
4 5 [e-mail]

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 [e-mail] 555687 568774
66 [e-mail] 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

[danbuchan]

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.

[Jx_Man]

if i did not wrong to understand :
select member_id, email_addr, tel_nr, fax_nr from STM_COMM where member_id = 5

[danbuchan]

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.

[nav33n]

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 group by staff.name

Wouldn't that work ?

[danbuchan]

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?

SELECT staff.name, review.DATE, promotion.DATE
FROM staff
LEFT JOIN review on staff.id=review.staff_id and review.date=(select max(review.date) from review where staff.id=review.staff_id)
LEFT JOIN promotion on staff.id=promotion.staff_id and promotion.date=(select max(promotion.date) from promotion where staff.id=promotion.staff_id)