Problem with Query Results

[txdave35]

Hey guys,

I am trying to write a query that will search for employees receiving payment from two agencies.

First, consider my table HISTORY:


Name AGENCY AMOUNT
_____ _______ _______

Harry Smith ATTS 12,000.03
Reed Jones RTSE 13,000.04
Harry Smith ASSP 5,000.23


With this data set, I want the record to only return the name Harry Smith since he has service in two different agencies.

I tried using count(*) > 1 on the Name field which did seem to work except for one problem. Some names have more than one record in the table for the same agency name. Such as:

Name AGENCY AMOUNT
_____ _______ _______

Jerry Smith ATTS 12,000.03
Reed Jones RTSE 13,000.04
Jerry Smith ATTS 5,000.23


How can I write a query to weed out these returns?

 

[gmmastros]

First, try Count(Distinct *). If that doesn't work for you, let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sasadear]

select a.name from a,b
where a.name =b.name and a.agency <>b.agency

A and B are the same table,copy A tale to B table

http://www.dearok.com

 

[txdave35]

select a.name from a,b
where a.name =b.name and a.agency <>b.agency


This appears to work! Can you explain how this statement works or point me to an SQL article that explains the technique? Thanks.

 

[SQLSister]

is it clearer if you use the form

select a.name from table1 a join table1 b
on.name =b.name where agency <>b.agency

It really is best to use the ANSII standard join syntax. This is called a self join where you join a table to itself.

"NOTHING is more important in a database than integrity." ESquared

 

[txdave35]

Thanks, I wasn't familiar with the self join concept.
One more question, my query results are returning two rows per name because they have the two agencies.

How can I tell SQL to only return one line per name? I really don't need to see the agency name in my query results. I just want to see a listing of all the names that have multiple agencies. Thanks guys.

 

[MDXer]

The following will return all Names with a distinct agency count greater than 1 if you want exactly 2 then use = 2

SELECT
a.name
FROM table1
GROUP BY a.name
HAVING COUNT(DISTINCT AGENCY) > 1