Using AND clause on same table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using AND clause on same table?

Author

Topic

sjmorgan
Starting Member

9 Posts

Posted - 2003-02-09 : 17:41:23

I have two tables that I want to return data from - employee_profile and taxonomy_sub.

The following query will return all employee's who have a selected taxonomy_sub ID in their profile:

SELECT DISTINCT employee.*, taxonomy_sub.sub_id
FROM employee
INNER JOIN employee_profile
ON employee.emp_id = employee_profile.emp_id
INNER JOIN taxonomy_sub
ON employee_profile.sub_id = taxonomy_sub.sub_id
WHERE (taxonomy_sub.sub_id = 512) OR (taxonomy_sub.sub_id = 527)

This is great, it returns several employees who have a sub_id as 512 or 527.

What I want to now do is return only those employees who have a sub_id of 512 AND 527 - from the results above I know I should get one row returned. However:

SELECT DISTINCT employee.*, taxonomy_sub.sub_id
FROM employee
INNER JOIN employee_profile
ON employee.emp_id = employee_profile.emp_id
INNER JOIN taxonomy_sub
ON employee_profile.sub_id = taxonomy_sub.sub_id
WHERE (taxonomy_sub.sub_id = 512) AND (taxonomy_sub.sub_id = 527)

Does not return any rows. I can't understand why and I have tried all sorts of different methods and searched a lot of help sites. What am I doing wrong??

HELP!

Thanks,

Stuart

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-09 : 17:56:02

Try:

SELECT DISTINCT employee.*
FROM employee
INNER JOIN
(SELECT emp_Id FROM
employee_profile
INNER JOIN taxonomy_sub
ON employee_profile.sub_id = taxonomy_sub.sub_id
WHERE taxonomy_sub.sub_id in (512,527)
GROUP BY emp_Id
HAVING COUNT(*) = 2) A
ON
employee.emp_id = A.emp_ID

Your query fails because of the AND in the WHERE clause -- a record will never have the same field equal to two different values. You want to return all records of sub_id 512 OR 527, group by Emp_ID, and if you get two records in that group, the emp_Id has both so that's what you want. that's why I created a sub-query and added the "HAVING COUNT(*) =2" clause.

- Jeff

sjmorgan
Starting Member

9 Posts

Posted - 2003-02-09 : 17:59:28

That is fantastic!

Thanks for the VERY fast response and it works 1st time - I have spent hours and hours trying things out!

Brilliant!

sjmorgan
Starting Member

9 Posts

Posted - 2003-02-10 : 08:45:22

Ok Well I thought this was working and it does for this situation, where the person will only have one unique taxonomy_sub.sub_id.

However, each taxonomy_sub.sub_id is linked to the taxonomy_main table. The person may have several sub taxonomy details under a main. E.g.:

taxonomy_main.main_id = 1
taxonomy_main.capability = "Language"

taxonomy_sub.sub_id = 456
taxonomy_sub.main_id = 1
taxonomy_sub.capability = "French"

taxonomy_sub.sub_id = 500
taxonomy_sub.main_id = 1
taxonomy_sub.capability = "Italian"

etc.

So if I wanted to list someone who has main_id 1 and main_id 5, the query will not work as I am getting two values back anyway with the match on main_id = 1.

Any Ideas?

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-10 : 16:50:10

you may have to add another layer to the subquery which does more GROUPING.

That is, make sure you filter as needed, and group just by Emp_Id and sub_id or whatever you can. then, do a query of THAT, group by emp_id, and make sure there are exactly two records in the group with the having clause.

Example:

instead of:

SELECT EmpID, sub_id, ...
FROM
table
WHERE sub_id in (1,2)
GROUP BY EmpID
HAVING COUNT(*) = 2

you may have to do something like:

SELECT EmpID
FROM
(
SELECT EmpID, sub_id, ...
FROM
table
WHERE sub_id in (1,2)
GROUP BY EmpID, sub_id
) A
GROUP BY EmpID
HAVING COUNT(*) = 2

in otherwords, add 1 more "layer" to your query to make sure you have the exact records you need.

- Jeff

sjmorgan
Starting Member

9 Posts

Posted - 2003-02-12 : 07:52:43

Thanks - this is what I ended up doing:

SELECT employee.emp_id, employee.firstname,
employee.surname, employee.telephone, employee.mobile, employee.email, employee.job_title, employee_team.team
FROM employee
INNER JOIN employee_team
ON employee.team_id = employee_team.team_id
INNER JOIN
(SELECT employee_profile.emp_id, taxonomy_sub.main_id
FROM employee_profile
INNER JOIN taxonomy_sub
ON employee_profile.sub_id = taxonomy_sub.sub_id
WHERE taxonomy_sub.sub_id = 432
OR taxonomy_sub.main_id = 5
GROUP BY employee_profile.emp_id, taxonomy_sub.main_id) A
ON employee.emp_id = A.emp_id
GROUP BY employee.emp_id, employee.firstname,
employee.surname, employee.telephone, employee.mobile,
employee.email, employee.job_title, employee_team.team
HAVING COUNT(main_id) = 2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources