get only one result with three different fields from same table 1

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

 

[PHV]

Perhaps SQL server admits the (NON ANSI) First aggregate function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MedievalKnight]

Hi,

I'm affraid the First (or last) aggregate functions do not exist in SQL server. Min and max are not correct.

 

[r937]

rows within a relational table have no position, so "first" has no meaning except as it may be based on some sequencing column (e.g. a datetime)

what you want is not possible with the columns you've shown

r937.com | rudy.ca

 

[MedievalKnight]

I have a unique ID (int) column and a created_date (datetime) column. The last one could be used then?

 

[r937]

yes, it could, except let's look at something else

consider these two rows --

id  member_id  datestamp         email_addr     tel_nr     fax_nr
------------------------------------------------------------------
1    5         2007-11-25 09:37  info@test.com
2    5         2007-11-26 12:34                 555687     568774

now the "first" row for member_id 5 has an email address, but it doesn't have any phone numbers

so now you're going to say to take the "first" value in each of the three columns, right?

r937.com | rudy.ca

 

[MedievalKnight]

Now, I have implemented it via a left outer join, selecting only those values that are not null. But, when you have 2 email adresses for instance, you'll see 2 rows comming back, and I only want the first created one. This is the query:


SELECT DISTINCT a.id, a.related_id, c.email_adres, c.tel_nr, c.gsm_nr
FROM STM_MEMBER a
LEFT OUTER JOIN STM_COMM c
ON (a.id = c.member_id AND NOT tel_nr IS NULL) OR
(a.id = c.member_id AND NOT gsm_nr IS NULL) OR
(a.id = c.member_id AND NOT email_addr IS NULL)
WHERE a.id = 1
GROUP BY a.id, a.related_id, c.email_adres, c.tel_nr, c.gsm_nr

Thnx 4 the help r937!

 

[MedievalKnight]

as an answer to your question: yes ;-)

 

[r937]

SELECT member_id
     , MAX(email_adres) AS email_adres
     , MAX(tel_nr)      AS tel_nr
     , MAX(gsm_nr)      AS gsm_nr
  FROM (
       SELECT member_id
            , email_adres
            , NULL AS tel_nr 
            , NULL AS gsm_nr 
         FROM STM_COMM AS e 
        WHERE datestamp = 
              ( SELECT MIN(datestamp)
                  FROM STM_COMM
                 WHERE member_id = e.member_id
                   AND email_adres > '' )   
      UNION ALL
       SELECT member_id
            , NULL AS email_adres
            , tel_nr 
            , NULL AS gsm_nr 
         FROM STM_COMM AS t 
        WHERE datestamp = 
              ( SELECT MIN(datestamp)
                  FROM STM_COMM
                 WHERE member_id = t.member_id
                   AND tel_nr > '' )   
       UNION ALL
       SELECT member_id
            , NULL AS email_adres
            , NULL AS tel_nr 
            , gsm_nr 
         FROM STM_COMM AS g 
        WHERE datestamp = 
              ( SELECT MIN(datestamp)
                  FROM STM_COMM
                 WHERE member_id = g.member_id
                   AND gsm_nr > '' )   
       ) AS d
GROUP
    BY member_id

r937.com | rudy.ca

 

[MedievalKnight]

This is awesome! Works like a charm.
I've been breaking my head on this the whole evening.
Thank you very much,

Christophe