Results 1 to 5 of 5

Thread: Problem of WHERE clause in SQL statement

  1. #1
    Join Date
    Nov 2009
    Posts
    47

    Problem of WHERE clause in SQL statement

    I need help with a subquery. The problem is to create the SQL statement to find employees in Department 10 with the same positions as in Title 30

    SELECT name, job
    FROM "table name"
    WHERE?

  2. #2
    Join Date
    Oct 2005
    Posts
    2,393

    Re: Problem of WHERE clause in SQL statement

    Now you have not shown us any table definitions, so if I write the query does not work then the blame lies not with me. Assuming that the column job is what you call position?

    Code:
    select name, job
    from table
    where Department=10
    intersect
    select name, job
    from table
    where Department=30

  3. #3
    Join Date
    Nov 2008
    Posts
    1,022

    Re: Problem of WHERE clause in SQL statement

    I have a better suggestion for you.

    Code:
    SELECT name, job
    FROM employees
    WHERE Department=10
    AND position =
    (
            SELECT position
            FROM employees
            WHERE Department=30
    )

  4. #4
    Join Date
    Oct 2005
    Posts
    2,393

    Re: Problem of WHERE clause in SQL statement

    The above query will not work. You should use "=" instead of IN the subquery. And whether you use the IN then it will return the wrong result since there is some correlation between rows in the outer and inner query. One can write about to use a subquery correlated with EXISTS (), but it is much simpler and more readable to use the Intersect, I think so.

  5. #5
    Join Date
    Nov 2008
    Posts
    1,054

    Re: Problem of WHERE clause in SQL statement

    But you have not given us any indication of which database you are on, but still trying on some tips. If it is oracle database and you want to do what you have asked, then you can try one of these two methods.

    Code:
    select name, job
    from table1
    where (name | | job) in (
    select (name | | job)
    from tabell2);
    or

    Code:
    select name,job
    from table1
    where (name,job)exist in
    select (name, job)
    from tabel2;
    Or you can of course use the Intersect as mentioned above. There are many ways to do the same on others. If it is MSSQL, then "||" should be replaced by "&" sign.

Similar Threads

  1. SQL statement problem in C#
    By AMISH in forum Software Development
    Replies: 4
    Last Post: 01-06-2010, 01:17 PM
  2. Problem with Prepared statement's 'setDate' method
    By Luis-Fernando in forum Software Development
    Replies: 5
    Last Post: 05-03-2010, 08:19 PM
  3. Problem with if statement in C++
    By Slender in forum Software Development
    Replies: 5
    Last Post: 29-01-2010, 09:57 PM
  4. Problem related to null value in sql statement
    By kamina23 in forum Software Development
    Replies: 4
    Last Post: 28-01-2010, 07:36 PM
  5. Use the CASE Statement in a SQL SELECT Clause
    By Abraham.J in forum Software Development
    Replies: 3
    Last Post: 12-08-2009, 01:52 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,698,379.16260 seconds with 17 queries