Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2004
    Posts
    2

    Unanswered: Select one if active else another

    I have a database of address info:
    IDNUM ADDTYPE SEQNUM STATUS STREET1 etc
    1 RS 1 123 Main
    1 BU 2 456 Main
    2 RS 1 I 789 Main
    2 BU 2 987 Elm
    and so on

    Some have have active RS (residence) and BU (business) addresses,
    some have only one or the other active.

    I need to select only one active address per IDNUM, If they have an active RS I need that one, if not then I can use the active BU.

    How can I select these without duplicating the records with both types active?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    assume STATUS='I' means inactive (this was my best guess, based on your information)
    Code:
    select ID
         , ADDTYPE
         , SEQNUM 
         , STATUS 
         , STREET1
      from yourtable as o
     where STATUS <> 'I'   /* check this */
       and ADDTYPE =
         ( select max(ADDTYPE)
             from yourtable
            where ID = o.ID
              and STATUS = o.STATUS )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Posts
    2

    Thank you

    That will do the trick.

    Thank you very much.

    I forget about using the max and min with anything but numbers.

  4. #4
    Join Date
    Feb 2005
    Location
    London
    Posts
    19

    Faster Version

    How about this ...?

    SELECT o.*
    FROM ADD_INFO AS o
    INNER JOIN ADD_INFO AS i
    ON o.IDNUM = i.IDNUM
    AND o.STATUS = i.STATUS
    AND o.ADDTYPE > i.ADDTYPE

    I think this will be faster if there are more records...

    What do you think r937??

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    jay82, you are not testing for active status

    plus, i think you will still need an outer join

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2005
    Location
    London
    Posts
    19
    You are right ... I don't think it's possible with even LEFT OUTER JOIN... but i am still trying...

  7. #7
    Join Date
    Feb 2005
    Location
    London
    Posts
    19
    Any problem with this query??

    SELECT o.*, i.*
    FROM ADD_INFO AS o
    LEFT OUTER JOIN ADD_INFO AS i
    ON o.IDNUM = i.IDNUM
    AND o.ADDTYPE <> i.ADDTYPE
    WHERE i.ADDTYPE IS NULL
    OR o.ADDTYPE > i.ADDTYPE
    AND o.STATUS <> 'I'

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, a major problem

    your ANDs and ORs need parentheses

    plus, i don't think the status is being handled correctly

    are you guessing, or testing?

    because the best way to develop queries is to test them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2005
    Location
    London
    Posts
    19
    Here's your parentheses ... and it's tested... but still if you are getting different result let me know... it will be good thing to learn... that's why we are here .. !

    SELECT o.*, i.*
    FROM ADD_INFO AS o
    LEFT OUTER JOIN ADD_INFO AS i
    ON o.IDNUM = i.IDNUM
    AND o.ADDTYPE <> i.ADDTYPE
    WHERE (i.ADDTYPE IS NULL
    OR o.ADDTYPE > i.ADDTYPE)
    AND o.STATUS <> 'I'

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what about "If they have an active RS I need that one, if not then I can use the active BU."

    you seem to be returning both of them

    and you're not testing i.STATUS either

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •