complex merging query in sql server 2000

Hello,

I have a Database in a SQL Server 2000 where I have different users
tables with equal fields like this:

id (int)
email (varchar)
name (varchar)
address (varchar)
joinedon (datetime)

I want to merge all the tables in one taking rows with the same email
but taking always the more recent fields based on the 'joinedon' field.
So if I have this four different rows:

Tbl email name address joinedon
----------------------------
T1 j@.smith.com johnathan NULL 01/01/95
T2 j@.smith.com NULL barcelona street 01/01/98
T3 j@.smith.com john valencia street 01/01/97
T4 j@.smith.com john Q NULL 01/01/99

And the final row entered in the new table would be

Tbl email name address joinedon
---------------------------
new j@.smith.com john Q barcelona street 01/01/99

I am trying doing his with union statements, but i am not getting the
real merging of data. Any clue?

Thanks for your help.Considering the following DDL and sample data:

CREATE TABLE TheTable (
id int PRIMARY KEY,
email varchar(50) NOT NULL,
name varchar(50) NULL,
address varchar(50) NULL,
joinedon datetime NOT NULL,
UNIQUE (email, joinedon)
)

INSERT INTO TheTable VALUES (1,
'j@.smith.com','johnathan',NULL,'19950101')
INSERT INTO TheTable VALUES (2, 'j@.smith.com',NULL,'barcelona
street','19980101')
INSERT INTO TheTable VALUES (3, 'j@.smith.com','john','valencia
street','19970101')
INSERT INTO TheTable VALUES (4, 'j@.smith.com','john Q',NULL,'19990101')

The following query provides the expected result:

SELECT x.email, (
SELECT a.name FROM TheTable a
WHERE a.email=x.email
AND a.joinedon=(
SELECT MAX(b.joinedon) FROM TheTable b
WHERE b.email=a.email AND b.name IS NOT NULL
)
) as name, (
SELECT c.address FROM TheTable c
WHERE c.email=x.email
AND c.joinedon=(
SELECT MAX(d.joinedon) FROM TheTable d
WHERE d.email=c.email AND d.address IS NOT NULL
)
) as address,
x.joinedon
FROM (
SELECT email, MAX(joinedon) as joinedon
FROM TheTable
GROUP BY email
) x

Razvan

mrclash wrote:

Quote:

Originally Posted by

Hello,
>
I have a Database in a SQL Server 2000 where I have different users
tables with equal fields like this:
>
id (int)
email (varchar)
name (varchar)
address (varchar)
joinedon (datetime)
>
I want to merge all the tables in one taking rows with the same email
but taking always the more recent fields based on the 'joinedon' field.
So if I have this four different rows:
>
Tbl email name address joinedon
----------------------------
T1 j@.smith.com johnathan NULL 01/01/95
T2 j@.smith.com NULL barcelona street 01/01/98
T3 j@.smith.com john valencia street 01/01/97
T4 j@.smith.com john Q NULL 01/01/99
>
And the final row entered in the new table would be
>
Tbl email name address joinedon
---------------------------
new j@.smith.com john Q barcelona street 01/01/99
>
I am trying doing his with union statements, but i am not getting the
real merging of data. Any clue?
>
Thanks for your help.