Query question - 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

Other Forums

MS Access

Query question

Author

Topic

dealwi8me
Starting Member

13 Posts

Posted - 2005-01-22 : 08:58:53

Hello all,
I have a question in Queries
How can i create a query with records that appears more than once in a table based on a field of the table?

An example:

Id | Num |
--------------
02653 | 5 |
--------------
65984 | 5 |
--------------
25589 | 2 |
--------------
43678 | 3 |
--------------
65983 | 2 |
--------------
65982 | 5 |
--------------
65980 | 2 |
--------------

If i execute a query based on Num ... i want to get these results...

Id | Num |
--------------
02653 | 5 |
--------------
65984 | 5 |
--------------
25589 | 2 |
--------------
65983 | 2 |
--------------
65982 | 5 |
--------------
65980 | 2 |
--------------

Any suggestions?
Thank you in advance!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-22 : 09:29:01

straightforward solution:
select id, num from table where id in(select id from table group by id having count(*)>1)

you might look up the EXISTS clause in BOL as well.

rockmoose

dealwi8me
Starting Member

13 Posts

Posted - 2005-01-23 : 06:29:27

"straightforward solution:
select id, num from table where id in(select id from table group by id having count(*)>1)"

in this solution i get "Type Mismatch in Expression" from MS Access... why is that?

jhermiz

3564 Posts

Posted - 2005-01-23 : 12:32:44

quote:
Originally posted by rockmoose

straightforward solution:
select id, num from table where id in(select id from table group by id having count(*)>1)

you might look up the EXISTS clause in BOL as well.

rockmoose

This wont work rocko since the ID numbers arent the same, grouping will return always true in this case since the count of each id > 0.

dealwi8me, try this:


SELECT YourTableName.Num, YourTableName.Id
FROM YourTableName
WHERE (((YourTableName.Num) In (SELECT Num FROM YourTableName As ADup GROUP BY Num HAVING Count(*)>1 )))
ORDER BY YourTableName.Num;

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-23 : 15:07:16

Correct Jon, i was rushing things.
Also I don't know about MS-Access syntax, maybe your query will work.

rockmoose

jhermiz

3564 Posts

Posted - 2005-01-23 : 15:53:57

quote:
Originally posted by rockmoose

Correct Jon, i was rushing things.
Also I don't know about MS-Access syntax, maybe your query will work.

rockmoose

Access does not have any knowledge of the EXISTS keyword.
My query should work since it's just plain old sql selects and using the IN predicate.

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-24 : 16:46:38

ms access knows keyword EXISTS;
rocko's query is ABSOLUTELY correct; he just misread the req.;
vodka is not cool and cavier is for the rich only;

jhermiz

3564 Posts

Posted - 2005-01-24 : 16:49:42

quote:
Originally posted by Stoad

ms access knows keyword EXISTS;
rocko's query is ABSOLUTELY correct; he just misread the req.;
vodka is not cool and cavier is for the rich only;

news to me

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-24 : 16:57:31

about vodka or cavier?

jhermiz

3564 Posts

Posted - 2005-01-24 : 21:42:11

quote:
Originally posted by Stoad

about vodka or cavier?

More along the lines of EXIST.
I dont drink

..nor do I like cavier, give me chilis chicken crispers and I'm happy

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-25 : 06:15:08

I'll have vodka and caviar any day!

Maybe MS Access will like this:

SELECT tab.Num, tab.Id
FROM tab
WHERE tab.Num = ANY(SELECT Num FROM tab GROUP BY Num HAVING Count(*)>1)

rockmoose

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-25 : 06:57:57

oho! rocko at large!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources