Author |
Topic |
dealwi8me
Starting Member
13 Posts |
Posted - 2005-01-22 : 08:58:53
|
Hello all,I have a question in QueriesHow 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.IdFROM YourTableNameWHERE (((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.IdFROM tabWHERE 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! |
 |
|
|