Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2009
    Posts
    5

    Unanswered: Need help writing this query please!

    I need to select all people with the code number 55 then from that group of people I only want to see the people from the first select code 55 but who also have had a code of 44 and 32.


    Query 1:
    Select employee_id FROM employees where codenum = '55'

    Query 2:
    Select employee_id FROM QUERY1 where codenum = '44' OR '32'

    I know where is a way to write this as one query with a sub query but I can seem to get it right. I would like to use IN cause for the codenum.

    Any help would be great!
    Rick

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sample rows please -- at least one employee which satisfies the conditions you're seeking, and one which doesn't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    There are a couple of ways I can think of (there may be others)
    Code:
    SELECT col-list
    FROM employees
    WHERE employee_id in(SELECT employee_id
                         FROM employees
                         WHERE codenum = '55'
                        )
    Code:
    SELECT col-list
    FROM employees A
    WHERE EXISTS (SELECT *
                  FROM employees B
                  WHERE A.employee_id = B.employee_id
                    AND codenum = '55'
                   )
    NOTE: this will include CODENUM = '55' rows in the final result. If you do not want these rows add AND codenum <> '55' to the outer query.

  4. #4
    Join Date
    Aug 2009
    Posts
    5

    Additional Information

    Maybe this might help:

    name | employee_id | codenum | department | reason

    Bob | 50 | 300 | service | retarted lab computer
    Bob | 50 | 255 | service | lunch break
    Mary | 23 | 255 | service | cleaning

    When I run the query I only want to see codenum 300 but I want to see everyone with a codenum 300 and all other records that the person with a 300 codenum have. I dont want to see someone with a codenum 255 unless they had a record with a codenum 300 at somepoint.

    Thanks for your guy's help!!

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    That explaination was more confusing than the first one. I think this is what you want:
    Code:
    SELECT col-list
    FROM employees
    WHERE employee_id IN(SELECT employee_id
                         FROM employees
                         WHERE codenum = '255'
                        )
      AND codenum <> '255'
    The sub query will find ALL employee_id values for codenum = '255'.
    This is used in the IN list of the outside query to find all those employee_id row but NOT any rows that have the employee_id = '255'.

    Is this what you want?

    PS the EXISTS also works by adding the AND codenum <> '255' to it.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This smells a lot like a refresher assignment for a second session SQL class to me. When is this assignment due? Is it posted on the web, or can you scan in all of the pages that the teacher gave you? We seem to be having trouble understanding this explanation of your assignment.

    What table contains the codenum column? I assume that codenum isn't in the employee table.

    My interpretation of your question is that you want to see a list of employees who meet one condition (55) and meet either of the other conditions (32 or 44). How close am I to understanding?

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

  7. #7
    Join Date
    Aug 2009
    Posts
    5
    LOL. No no... this is a not a classroom assignment. I'm working on a project.

    You are close to what I am looking for but not yet... I think I have a better way to explain.

    Query 1: Get all employees with codenum 50 <--- Think of this as a table of people with only codenum of 50.

    Query2: Get all records WHERE employee_ID IN Query1 WHERE codenum = 255

    SELECT * FROM employees WHERE codenum = '50' <--- I need to use the results of this query in a where clause to filter the database again to get only the records belonging to those in the result of the first query.

    Remember they will have a codenum = 50 but they will also have other records with different codenums and that is the list I need.


    Employees can have any number of records with different codenums. As the supervisor of my department I only want to see employees with codenum of 255 but I want to see all of their other records because they had a codenum 255.

    I hope this helps... it is super confusing...

  8. #8
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    simplyrichard, from your explanation, the last query I supplied should get you the results you want. Find all the employees (employee_ID) that have a codenum '255' row. Use this in the Where clause (with an IN or an EXISTS) to find all rows that are in this list of employee_ID values from the sub-query) but filter out any codenum '255' rows.

    Try it and let us know if something isn't working as expected.

  9. #9
    Join Date
    Aug 2009
    Posts
    5
    I will test this query tomorrow at work. I will keep you posted

  10. #10
    Join Date
    Aug 2009
    Posts
    5

    Thanks!

    Stealth_DBA your query was correct. As it turns out it is the same as mine that I had wrote but mine kept returning incorrect data and I used IN clauses. The problem was in where I was placing the date range in my where clause.

    (BTW sorry the scheme changed I didn't have the correct feilds at home)

    Code:
    SELECT     PatID, Pat_Name
    FROM         Pat_Info
    WHERE     (Revenue_Id IN ('3920', '3056')) AND (Encounter IN
                              (SELECT     Encounter
                                FROM          Pat_Info AS Pat_Info2
                                WHERE      (Revenue_Id IN ('3920')) AND (Charge_Date BETWEEN '8/24/2009' AND '8/24/2009')))
    ORDER BY Pat_Name
    The above is the working query for anyone else that might need an example of selecting a group of people and then using those people in a where clauses to pull additional records with a subquery.

    Thanks for all your help!

    Richard

Posting Permissions

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