Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2013
    Posts
    2

    Unanswered: SQL query question

    Hi,

    I have two tables

    Employee(
    userID number,
    name varchar
    )

    EmployeeDetails(
    userID foreign key to Employee.userID,
    age number,
    dateOfBirth)

    I want the user details of all those employees which have date of birth greater than 01-01-1980

    Selecting the data would be fine with this query

    select userID, dateOfBirth
    from Employee A
    join EmployeeDetails B
    on
    A.userID = B.userID
    where
    B.dateOfBirth > '01-01-1980';

    But I want the output in following format. If the dob is greater than 01-01-1980 then "Yes" should be present in a new column or else "No"

    userID Eligible
    10000 Yes
    20000 No

    Hope my point is clear.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT userID, 'Yes'
       FROM EmployeeDetails
       WHERE '1980-01-01' < dateOfBirth
    UNION SELECT userID, 'No'
       FROM EmployeeDetails
       WHERE  dateOfBirth <= '1980-01-01';
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    or just access the table once since above would most likely be two tablespace scans. Also, why the join with employee table? No need seen to do it.

    select userid, case when dateOfBirth <= '1980-01-01' then 'no else 'yes' end
    from EmployeeDetails
    Dave

  4. #4
    Join Date
    Oct 2013
    Posts
    2

    Further analysis

    Thanks for the quick responses.

    I tried both the approaches suggested by dav1mo and Pat Phelan.

    1.dav1mo: Using UNION

    It seems Union was much easier to understand and implement but with joins it became a bit clustered.

    For example when I want the name also in the output, then I had to join the two tables twice


    select A.userID, A.name, 'Yes' as Eligible
    from employee A,
    employeeDetails B
    where dob > '1980-01-01'
    and
    a.userID = B.userID
    UNION
    select A.userID, A.name, 'No' as Eligible
    from employee A, employeeDetails B
    where dob < '1980-01-01'
    and
    A.userID = B.userID;


    +--------+-------+----------+
    | userID | name | Eligible |
    +--------+-------+----------+
    | 10000 | Peter | Yes |
    | 20000 | John | No |
    +--------+-------+----------+
    2 rows in set (0.00 sec)



    2.Pat Phelan: Using Case

    This method seems to be more compact and quick. I was not aware of the "case" function. First I thought its a database specific function. I tried this in DB2 and mysql: It works for both.


    select A.userID, name,
    CASE
    when dob < '1980-01-01'
    then 'No' else 'Yes'
    end as Eligible
    from employee A,
    employeeDetails B
    where A.userID = B.userID;


    +--------+-------+----------+
    | userID | name | Eligible |
    +--------+-------+----------+
    | 10000 | Peter | Yes |
    | 20000 | John | No |
    +--------+-------+----------+



    In my opinion the first would be appropriate if joins are not required and the second if joins are there.

    I was also wondering if the case can be used to test multiple conditions also!

    If I am wrong anywhere, please let me know.
    Regards,
    amiit8

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    CASE is supported by most SQL engines, although a few have been slow to pick it up. It processes whatever logical statements that the engine supports, so complex logic is permitted in most engines.

    UNION is simple, CASE is more flexible/powerful.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You can include the same joins in either solution. I just removed them as in the SQL specified they were not needed for anything.
    Dave

Tags for this Thread

Posting Permissions

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