Post reply

Help, question on returning two addresses from the same table in one record

  • August 5, 2008 at 3:21 am

    #191982

    Hello,

    I have a question, im having problems returning data in a single record from a single table. I have two records per customer in the table, with different address id's but the same customer ref and product id. when I try to use a join to bring back one record with customer details and both address I get two records, with the addresses showing : address1, address2 and address2, address1. My query is as below and I would welcome any info you can give me.

    Thanks

    Chris

    SELECT DISTINCT

    i.CUSTOMER_REF,

    i.COMPANY_NAME,

    i.ACCOUNT_NUM,

    i.LEGACY_ACCOUNT_NO,

    i.PRODUCT_SEQ,

    i.PRODUCT_ID,

    i.PRODUCT_NAME,

    i.START_DAT,

    i.END_DAT,

    i.BILLING_STATUS,

    i.ADDRESS_SEQ,

    i.ADDRESS_1,

    i.ADDRESS_2,

    i.ADDRESS_3,

    i.ADDRESS_4,

    i.ADDRESS_5,

    i.POSTCODE,

    i2.ADDRESS_SEQ_1,

    i2.ADDRESS_2_1,

    i2.ADDRESS_2_2,

    i2.ADDRESS_2_3,

    i2.ADDRESS_2_4,

    i2.ADDRESS_2_5,

    i2.POSTCODE_2,

    i.PRODUCT_ATTRIBUTE_SUBID,

    i.ATTRIBUTE_UA_NAME,

    i.ATTRIBUTE_VALUE

    FROM dbo.All_QueryInfo_export_FULL AS i

    INNER JOIN dbo.All_QueryInfo_export_FULL AS i2

    ON i.CUSTOMER_REF = i2.CUSTOMER_REF

    AND i.PRODUCT_ID = i2.PRODUCT_ID

    AND i.PRODUCT_SEQ = i2.PRODUCT_SEQ

    AND i.ADDRESS_SEQ <> i2.ADDRESS_SEQ_1

  • August 5, 2008 at 3:35 am

    #853071

    Change the last line to:

    AND i.ADDRESS_SEQ < i2.ADDRESS_SEQ

    Explanation: If you have two addresses A and B, then as teh two are not the same you will get back both A,B and B,A. If you force an ordering A<B, then you get only A,B

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • August 5, 2008 at 3:43 am

    #853075

    WOW ! happy days... it worked, thankyou very much !:D

    So I presume that is always the case with that kind of data ? Oh your a life saver, id tried everything I knew to get this sorted, thank you.

  • August 5, 2008 at 4:34 am

    #853088

    With that surname you have to be the same Chris that worked with me for a short time at Sema in Liverpool - am I right?

    If yes, how goes it? Last I heard you had moved to Brussels - still there?

    Chris Quinn

  • August 5, 2008 at 4:54 am

    #853093

    Blimey ! 🙂

    It is indeed my friend, how the devil are you ?!?! No im no longer in Brussels, been around a bit since then too but now back in UK, where are you these days, still in Sema ?

  • August 5, 2008 at 5:04 am

    #853095

    I've not been with Sema for over six years - I took redundancy in 2002 😀 and went to the Criminal Records Bureau for about 10 weeks, then went to the Blue Planet Aquarium as IT Manager for two years before they made me redundant. 🙁

    I've been working as an Access/SQL developer for a firm in Chester for the last three years

    Sema were taken over a couple of years ago and are now part of Atos Origin - there's still a small team working out of Old Hall Street, and Wilmslow is still up and running - I bump into people from time to time, and actually now work with a guy who worked at OHS

  • April 7, 2010 at 3:59 am

    #1147163

    Hi Chris

    Hope its you .............. wondered where you have got to??? Have you left Atos Origin?

    Liz

    (you know who I am!!!!!!!!!!)

  • April 7, 2010 at 7:59 am

    #1147347

    Which Chris?

  • April 7, 2010 at 8:26 am

    #1147370

    Chris Quinn of course ??????????????????????????????????????????

    It must be you - your dry sense of humour .........

    😉

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply