#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0

    SQL query question


    Select ent.name, ent.DOB, add.Add_type, add.Address
    From Entity ent, Address add where ent.entity_ref = add.ref
    and add.add_type = ‘MA’

    The above query only returns records from the Address table where add_type = ‘MA’. I should like to expand the query to include add_type = ‘RA’ where add_type = ‘MA’ does not exist, as in entity_ref ‘e4’ in the Address table.
    I have tried adding if, not exist, else, or, statements but cannot get it to bring back the results I need. Can you please help?
    Entity
    entity_ref Name DOB
    e1 John Marr 25.10.86
    e2 Jane Rose 4.5.69
    e3 Dan Mott 5.11.56
    e3 Ann Twin 24.8.49
    Address


    entity_ref Add_type Address
    e1 MA 10 Riling Road
    e1 RA 72 Marston Place
    e2 MA Apt 6 Slough Road
    e2 RA 56 Deen St
    e3 MA 52 Gott Road
    e3 RA 1 Manor St
    e4 RA 23 Leyton Square
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    173
    Rep Power
    14
    Code:
    Select m.name, m.DOB,
    coalesce(a1.Add_type,a2.add_type) as add_type, 
    coalesce(a1.address,a2.address) as address
    From entity m 
    left join Address a1 on a1.entity_ref = m.ref and a1.add_type = 'MA' 
    left join Address a2 on a2.entity_ref = m.ref and a2.add_type = 'RA'
    If you can dream it, you can create it; if you can believe it, you can become it. --Anon
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0
    Thanks, I will give it try....

IMN logo majestic logo threadwatch logo seochat tools logo