Help on double contacts in my database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help on double contacts in my database

Author

Topic

Grammy
Starting Member

5 Posts

Posted - 2008-06-03 : 07:34:56

Hello!

I am currently workin on clearin a litte in our data collation, Can anyone tell me how i will get this to work..
I have this selected so far [URL=http://img247.imageshack.us/my.php?image=sqlresultatct2.png]

[/URL]

But i only want those selected who have the highest value in the last 3 columns in relation with their counterpart.... Anyway of doin that??

this is what i have to get the result-set

SELECT contact.contact_id,
        contact_p.p_name,
        contact_adr.adr_zip_code,
        contact_p.p_first_name,
        contact_p.p_last_name,
        form_land_i_os.cdp_1y81__,
        form_land_i_os.cdp_totalha,
        form_land_i_os.cdp_ha_krak
FROM contact,
        contact dub_contact,
        contact_p,
        contact_p dub_contact_p,
        contact_adr,
        contact_adr dub_contact_adr,
        form_land_i_os,
        form_land_i_os dub_form_land_i_os,
        po_userdw
WHERE (contact.deleted IS NULL AND
        dub_contact.deleted IS NULL AND
        dub_contact_adr.adr_zip_code IS NOT NULL AND
        contact.contact_status = 3 AND
        dub_contact.contact_status = 3 AND
        contact.p_id = contact_p.p_id AND
        dub_contact.p_id = dub_contact_p.p_id AND
        contact.adr_id = contact_adr.adr_id AND
        dub_contact.adr_id = dub_contact_adr.adr_id AND
        form_land_i_os.id = po_userdw.id AND
        dub_form_land_i_os.id = po_userdw.id AND po_userdw.relation_id = contact.contact_id AND
        contact_adr.adr_zip_code = dub_contact_adr.adr_zip_code AND
        contact_p.p_first_name = dub_contact_p.p_first_name AND
        contact_p.p_last_name = dub_contact_p.p_last_name AND      
        contact.contact_id <> dub_contact.contact_id AND
        contact.adr_id <> dub_contact.adr_id AND
        contact_p.p_name <> 'X')
ORDER BY contact_p.p_name

Thank you in advance wise gentlemen

Dont cross the road, if you cant get out of the kitchen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 07:40:58

Cant view image here.imageshack is blocked here

Can you post some sample data?

Grammy
Starting Member

5 Posts

Posted - 2008-06-03 : 07:57:21

Dont cross the road, if you cant get out of the kitchen

Grammy
Starting Member

5 Posts

Posted - 2008-06-03 : 07:57:52

Damn... dont know how to make it bigger

Dont cross the road, if you cant get out of the kitchen

Grammy
Starting Member

5 Posts

Posted - 2008-06-03 : 08:06:25

[code]contact_id p_name adr_zip_code p_first_name p_last_name cdp_1y81__ cdp_totalha cdp_ha_krak
'uc_94289' 'Frode Kok' '4863' 'Frode' 'Kok' 37.0 41.0 44.0
'49a4_r' 'Frode Kok' '4863' 'Frode' 'Kok' 0.0
'dfe6_r' 'Gunnar Due Pedersen' '6541' 'Gunnar' 'Pedersen' 0.0,,
'dz01_r' 'Gunnar Due Pedersen' '6541' 'Gunnar' 'Pedersen' 50.0 64.0 68.0[/code]

Dont cross the road, if you cant get out of the kitchen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 08:16:43

[code]SELECT contact_id,
p_name,
adr_zip_code,
p_first_name,
p_last_name,
cdp_1y81__,
cdp_totalha,
cdp_ha_krak
FROM (
SELECT contact.contact_id,
contact_p.p_name,
contact_adr.adr_zip_code,
contact_p.p_first_name,
contact_p.p_last_name,
form_land_i_os.cdp_1y81__,
form_land_i_os.cdp_totalha,
form_land_i_os.cdp_ha_krak,
ROW_NUMBER() OVER (PARTITION BY contact.contact_id ORDER BY form_land_i_os.cdp_1y81__ + form_land_i_os.cdp_totalha + form_land_i_os.cdp_ha_krak DESC) AS RecID
FROM contact
INNER JOIN contact AS dub_contact ON dub_contact.contact_id <> contact.contact_id
AND dub_contact.adr_id <> contact.adr_id
AND dub_contact.contact_status = 3
INNER JOIN contact_p ON contact_p.p_id = contact.p_id
AND contact_p.p_name <> 'X'
INNER JOIN contact_p AS dub_contact_p ON dub_contact_p.p_id = dub_contact.p_id
AND dub_contact_p.p_first_name = contact_p.p_first_name
AND dub_contact_p.p_last_name = contact_p.p_last_name
INNER JOIN contact_adr ON contact_adr.adr_id = contact.adr_id
INNER JOIN contact_adr AS dub_contact_adr ON dub_contact_adr.adr_id = dub_contact.adr_id
AND dub_contact_adr.adr_zip_code = contact_adr.adr_zip_code
INNER JOIN po_userdw ON po_userdw.relation_id = contact.contact_id
INNER JOIN form_land_i_os ON form_land_i_os.id = po_userdw.id
INNER JOIN form_land_i_os AS dub_form_land_i_os ON dub_form_land_i_os.id = po_userdw.id
WHERE contact.deleted IS NULL
AND contact.contact_status = 3
AND dub_contact.deleted IS NULL
AND dub_contact_adr.adr_zip_code IS NOT NULL
) AS d
WHERE RecID = 1
ORDER BY p_name[/code]

E 12°55'05.25"
N 56°04'39.16"

Grammy
Starting Member

5 Posts

Posted - 2008-06-03 : 08:29:43

Wow Thank you!

Dont cross the road, if you cant get out of the kitchen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources