omitting data

How to omit data from a query

Page 1 of 1

4 Replies - 972 Views - Last Post: 31 October 2010 - 09:06 AM

#1 Mark.Scotland  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 08-June 09

omitting data

Posted 29 October 2010 - 09:30 AM

Had the following question at Uni:

List first name, surname, salary and department id of all employees who either work in the same department or earn the same salary as employee “Peter Hall”.

The following works ok.

SELECT first_name, last_name, salary, department_id
FROM employees
WHERE department_id IN
(SELECT department_id
FROM employees
WHERE first_name LIKE 'Peter'
AND last_name LIKE 'Hall')
OR salary IN
(SELECT salary
FROM employees
WHERE first_name LIKE 'Peter'
AND last_name LIKE 'Hall');

Second part of the question is:

Same as above but don’t show employee “Peter Hall”.

I tried the following but had the same results.

SELECT first_name, last_name, salary, department_id
FROM employees
WHERE first_name NOT LIKE 'Peter'
AND last_name NOT LIKE 'Hall'
AND department_id IN
(SELECT department_id
FROM employees
WHERE first_name LIKE 'Peter'
AND last_name LIKE 'Hall')
OR salary IN
(SELECT salary
FROM employees
WHERE first_name LIKE 'Peter'
AND last_name LIKE 'Hall');

I think I'm having trouble with the order of the AND / OR
Any help with this would be greatly appreciated.


Is This A Good Question/Topic? 0
  • +

Replies To: omitting data

#2 Mark.Scotland  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 08-June 09

Re: omitting data

Posted 30 October 2010 - 03:25 AM

View PostMark.Scotland, on 29 October 2010 - 08:30 AM, said:

Had the following question at Uni:

List first name, surname, salary and department id of all employees who either work in the same department or earn the same salary as employee “Peter Hall”.

The following works ok.
SELECT first_name, last_name, salary, department_id 
FROM employees
WHERE department_id IN
         (SELECT department_id
          FROM employees
          WHERE first_name LIKE 'Peter'
          AND last_name LIKE 'Hall')
OR salary IN
         (SELECT salary
          FROM employees
          WHERE first_name LIKE 'Peter'
          AND last_name LIKE 'Hall');


Second part of the question is:

Same as above but don’t show employee “Peter Hall”.

I tried the following but had the same results.
SELECT first_name, last_name, salary, department_id 
FROM employees
WHERE first_name NOT LIKE 'Peter'
AND last_name NOT LIKE 'Hall'
AND department_id IN
         (SELECT department_id
          FROM employees
          WHERE first_name LIKE 'Peter'
          AND last_name LIKE 'Hall')
OR salary IN
         (SELECT salary
          FROM employees
          WHERE first_name LIKE 'Peter'
          AND last_name LIKE 'Hall');


I think I'm having trouble with the order of the AND / OR
Any help with this would be greatly appreciated.

Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5826
  • View blog
  • Posts: 12,681
  • Joined: 16-October 07

Re: omitting data

Posted 30 October 2010 - 05:42 AM

Your first query is pretty bad. You're doing three queries against the same table, with the same where clause, for not apparent reason. Also, LIKE only makes sense with a wild card. The use of IN and a sub query should be a method of last resort.

Your first query does this:
SELECT a.first_name, a.last_name, a.salary, a.department_id 
	FROM employees a
		INNER JOIN employees b
		 ON a.department_id = b.department_id
			AND a.salary=b.salary
			AND b.first_name='Peter' 
			AND b.last_name='Hall'




To edit out the employee for query b, you should just be able to add a where clause
SELECT a.first_name, a.last_name, a.salary, a.department_id 
	FROM employees a
		INNER JOIN employees b
		 ON a.department_id = b.department_id
			AND a.salary=b.salary
			AND b.first_name='Peter' 
			AND b.last_name='Hall'
	WHERE a.first_name!=b.first_name
		AND a.last_name!=b.last_name


Was This Post Helpful? 0
  • +
  • -

#4 Mark.Scotland  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 08-June 09

Re: omitting data

Posted 31 October 2010 - 08:43 AM

Yours worked fine for the query 'work in the same department and earn the same salary as employee “Peter Hall”'

But what I'm trying to work out is:
'work in the same department or earn the same salary as employee “Peter Hall”'
Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5826
  • View blog
  • Posts: 12,681
  • Joined: 16-October 07

Re: omitting data

Posted 31 October 2010 - 09:06 AM

If your logic as all AND or only two statements, you can just string the things together without worry. If you have both AND and OR in more than two statements, then it's time to break out the parenthesis.

SELECT a.first_name, a.last_name, a.salary, a.department_id
    FROM employees a
        INNER JOIN employees b
         ON ( a.department_id = b.department_id OR a.salary=b.salary )
            AND b.first_name='Peter'
            AND b.last_name='Hall'


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1