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.
| Author |
Topic |
|
pedro
Starting Member
1 Post |
 Posted - 2005-10-05 : 05:34:51
|
| I have a simple table (uid int, aid int, val varchar) i.e.1 1 A1 2 B2 1 A2 2 C3 1 D3 2 BWhat I want to do is retrieve all the unique uid values for a number of different aid val combinations. For example all the uids that have both (aid=1 and val=A) and (aid=2 and val=B)I can code this with Having and Group by, but it seems a bit slow as the table is kind of large.e.g.SELECT uid FROM tableWHERE (aid=1 and val='A')OR (aid=2 and val='B')GROUP BY uidHAVING count(uid) = 2Another option I tried is to create a temporary table, insert uids that have aid=1 and val=A, then delete any uids that don't have aid=2 and val=B. This seems to run faster.Just wondered if anyone had any idea how I can code it up as a more efficient single sql statement.Or maybe I'm just trying to do something that sql doesn't really want to do.thanks. |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-10-05 : 05:43:41
|
| how did your query work?aid and val are not even included in the resultsetanyways...select uid from tablegroup by uidhaving uid in (SELECT uid FROM tableWHERE (aid=1 and val='A')OR (aid=2 and val='B'))and count(*)=2haven't tested this though...--------------------keeping it simple... |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-05 : 06:44:12
|
| >>but it seems a bit slow as the table is kind of large.Did you use index for key column?>>For example all the uids that have both (aid=1 and val=A) and (aid=2 and val=B)But you used Or in your queryPost your expected resultMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Efficient way to retrieve groups of records ?