Query Differing Data - SQL Server Forums

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

Query Differing Data

Author

Topic

yawnzzzz
Starting Member

13 Posts

Posted - 2009-07-06 : 12:01:06

Hi,

I'm new to SQL, and I have a question. I have a table... let's say I have two fields, one called Name and one called Age. I want to write a query that if the Name is the same for two records and the age is different, then it returns those records.

For example:

Name Age
John 20
Jim 21
Jim 21
John 30
Jane 18

Would return:
John 20
John 30

Returning 'John' would be even better, but either result is fine. Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-06 : 12:05:37

RFH?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-06 : 12:16:42

In any case...here's a hack

What if there's more than 2?


SET NOCOUNT ON

DECLARE @myTable99 table ([Name] varchar(50), Age int)

INSERT INTO @myTable99([name], Age)
SELECT 'John',	20	UNION ALL
SELECT 'Jim',	21	UNION ALL
SELECT 'Jim',	21	UNION ALL
SELECT 'John',	30	UNION ALL
SELECT 'Jane',	18

SELECT * 
  FROM @myTable99 a INNER JOIN @myTable99 b
	ON a.[Name] = b.[Name]
 WHERE a.[Name] IN
	( SELECT [Name]
		FROM @myTable99
	GROUP BY  [Name]
	  HAVING COUNT(*) > 1)
    AND a.Age <> b.Age

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-06 : 12:18:20

1. group by Name and Age and ignore the ones where count > 1
2. group by Name on this resultset and select the ones where count > 1

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-06 : 12:57:34

OK, give it a shot and post what you've tried...I've given you some samples..you should be good from here

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

yawnzzzz
Starting Member

13 Posts

Posted - 2009-07-06 : 13:58:57

My problem wasn't really related to age/name, but I just used that to make it simpler. I've been able to translate everything into a huge SQL query, and it runs well until I run into another hitch.

I've seen where you can put brackets around a column name that happens to be a keyword... but this database has a table as a keyword. The table is named:

If I do:

SELECT * FROM ABCDBO.[PLAN]

It works... but if I want the table to be referred to by a letter (ex. ABCDBO.PLAN g), I can't seem to get it to work correctly. Any help would be appreciated.

yawnzzzz
Starting Member

13 Posts

Posted - 2009-07-06 : 14:01:27

Nevermind that last post. It was just a typo.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources