Post reply

select statement and preferred postal address

  • September 11, 2006 at 5:34 am

    #113569

    i have given a a database of addresses and contacts. we are using MS sql 2000.  contacts table has columns (simplified):

    personref, addressID.

    addresses table contains all addresses concerning that person with columns (simplified):

    id,personref,addressline1,defaultaddress

    i need a select statement to find all address. usually a simple thing to do but i show my "beginner status" when i find the developer has set addressID=-2 when we should write to the defaultaddress (flagged with 'Y') and addressID=-1 when there is no address entered for the person.

    my initial view to find all address for the contacts table is:

    select c.personref, c.addressline1 from contacts c inner join addresses a on c.addressID=a.id where c.addressID>-1

    UNION

    select c.personref, c.addressline1 from contacts c inner join addresses a on c.personref=a.personref where c.addressID<-1 and a.defaultaddress='Y'

    i have assumed that where there is no address provided we use the default address.

    i apologise in advance as i have not actually tested the above select statement. however i am very interested in knowing if i am dealing with this in the correct manner or might there be a better way. by better i mean faster or less wordy.

    any help appreciated. thank you.

  • September 11, 2006 at 6:43 am

    #659599

    I think you have it... the only thing that I would change is UNION to UNION ALL since it's not likely that there will be any overlap between the two SELECTs.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 11, 2006 at 8:26 am

    #659664

    thanks Jeff for the help

Viewing 3 posts - 1 through 2 (of 2 total)

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