Question about displaying only multiple records

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

General SQL Server Forums

New to SQL Server Programming

Question about displaying only multiple records

Author

Topic

potn1
Starting Member

33 Posts

Posted - 2011-03-23 : 18:25:03

I have a query that I am compiling and these would be some example results from the table:

PersonID Sport
-------- -----
1 Basketball
1 Football
2 Baseball
3 Baseball
3 Basketball
4 Football

Basically I want only results to show where a specific 'PersonID' has more than 1 sport listed. So for example I'd like to compile some sort of statement where 'PersonID' 2 and 4 would not be shown because they only have one sport. I have been working with COUNT, GROUP BY AND HAVING aggregate functions because I believe that is some sort of a start.

Anyone have any suggestions.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-23 : 18:37:57

What have you tried that isn't working? You listed all the tools you need to solve your query!

Jim

Everyday I learn something that somebody else already knew

K-SaMa
Starting Member

7 Posts

Posted - 2011-03-23 : 18:55:43

Here Ya Go :

SELECT PERSON_ID
FROM PEOPLE
GROUP BY PERSON_ID
HAVING COUNT('X')>1;

potn1
Starting Member

33 Posts

Posted - 2011-03-24 : 10:27:29

quote:
Originally posted by K-SaMa

Here Ya Go :

SELECT PERSON_ID
FROM PEOPLE
GROUP BY PERSON_ID
HAVING COUNT('X')>1;

That above statement works when I am querying only that specific table. In my actual query I am grabbing data from 3 different tables and then joining them on the Person_ID. When I do this it is only displaying results that are similar in every column. For instance I have about 25 records and only the records where the Person_ID and every other column is the same is being displayed. My example result when I use the above query posted is 1 user being displayed because they have every field similar for 2 different records.

If a Person_ID is listed twice with different records for each instance both records need to be displayed. If a Person_ID is only listed once with records they do not need to be displayed. I only want PersonID's listed more than once and I want both records to be shown.

potn1
Starting Member

33 Posts

Posted - 2011-03-24 : 11:41:03

quote:
Originally posted by K-SaMa

Here Ya Go :

SELECT PERSON_ID
FROM PEOPLE
GROUP BY PERSON_ID
HAVING COUNT('X')>1;

I believe I found my solution. I needed to run my initial query and then use the above statement you gave me as a subquery.

AND PERSON_ID IN(SELECT PERSON_ID FROM PEOPLE GROUP BY PERSON_ID HAVING COUNT('X')>1)

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources