Results 1 to 7 of 7

Thread: Union explained

  1. #1
    Join Date
    Jun 2011
    Posts
    18

    Unanswered: Union explained

    Hi Guys,

    I am working with this syntax and would like to know what the code below is doing :-

    select LAST_NAME,
    FIRST_NAME,
    MI,
    SSN "SSNorBirthDate"
    from name,
    birth
    where ( nameID = birthID )
    and ( SSN is not null
    and name_change_ind is null )
    union
    select LAST_NAME,
    FIRST_NAME,
    MI,
    to_char(birth_BIRTH_DATE,'DD-MON-YYYY')
    from name,
    birth
    where ( nameID = birthID )
    and ( SSN is not null
    and name_change_ind is null )

    order by 1

    My confusion is on the fourth select of the query, one is calling the SSN number and the other is calling the Birthdate.

    Sincerely
    eenje

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    first of all, there are only two SELECTs in that union query, not four

    notice that the FROM and WHERE clauses of the two SELECTs are identical

    what this query is doing is pulling out two result rows for each joined row, one containing the value of the SSN column, and the other containing the character value of the birth_BIRTH_DATE column

    note that these two values occupy the same column in the union result set
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2011
    Posts
    18

    union explained

    So my confusion come to - which field is going to be displayed and why? what if both fields have information

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >So my confusion come to - which field is going to be displayed and why?
    run posted SQL to see answer yourself
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2011
    Posts
    18

    Union explained

    Well as long as there are ppl like you, ppl like me will continue trying to learn. No offence taken, I am here to learn n u can say what you want, as the saying goes - you can drag the donkey to the river but you can force him to drink

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by eenje View Post
    I am here to learn
    so run the query, inspect the results, and you will soon discover what you wanted to know -- "which field is going to be displayed and why? what if both fields have information"

    just looking at the results should answer this

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

  7. #7
    Join Date
    Jun 2011
    Posts
    18

    union explained

    I saw the answer, actually - what happened was the first time i ran the query it was displaying fields that dont have either ssn or dob so i got confused at to what was happening. Moving along and learning. Thx guys

Posting Permissions

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