Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2008
    Posts
    40

    Unanswered: Condition Column

    Code:
    SELECT E.EMPID, PHONENUMBER, N'In' AS TYPE
    FROM EMPLOYEE E
    WHERE M.EMPID IN (SELECT B.EMPID FROM BUILDING B)
    UNION ALL
    SELECT E.EMPID, PHONEUNUMBER, N'Not In' AS TYPE
    FROM EMPLOYEE E
    WHERE M.EMPID NOT IN (SELECT B.EMPID FROM BUILDING B)
    Code:
    Employee
    EMPID	PHONENUMBER
    
    BUILDING
    EMPID
    I tried to select Employee ID, Phone Number, and whether the Employee is in building table or not. Result Supposed to look following. Query works but I believe there is better way to do this. Any one got any improvement?

    John, 7014841524, IN
    KIM, 1112222224, NOT IN

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT employee.empid
         , employee.phonenumber
         , CASE WHEN building.empid = employee.empid
                THEN N'In'
                ELSE N'Not In' END
      FROM employee 
    LEFT OUTER
      JOIN building
        ON building.empid = employee.empid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2008
    Posts
    16
    SELECT E.EMPID, PHONENUMBER, N'In' AS TYPE
    FROM EMPLOYEE E , BUILDING B
    WHERE E.EMPID = B.EMPID
    UNION
    SELECT E.EMPID, PHONEUNUMBER, N'Not In' AS TYPE
    FROM EMPLOYEE E , BUILDING B
    WHERE E.EMPID != B.EMPID

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vijayarajvp View Post
    ...
    UNION
    SELECT E.EMPID, PHONEUNUMBER, N'Not In' AS TYPE
    FROM EMPLOYEE E , BUILDING B
    WHERE E.EMPID != B.EMPID
    you haven't actually tested this, have you

    because what this produces is garbage
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    You can use case statement along with inner join to increase the performance of this query

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jassi.singh View Post
    You can use case statement along with inner join to increase the performance of this query
    inner joins do ~not~ "increase the performance"

    and inner join is ~not~ appropriate here

    and CASE is not a statement, it's an expression, but this is a minor point compared to the other egregiously bad advice you've given


    jassi, i really question if you understand this problem at all

    may i suggest that you actually test your solutions before posting them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2008
    Posts
    40
    thank you r937

Posting Permissions

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