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 |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2004-01-17 : 19:13:22
|
| First, I apologize for not posting a sample schema/data. I'd be happy to if the question is more than trivial to the gurus around here.I've got a vexing problem: a query that joins an ungodly number of tables, and to which I've recently added another join. This new join is in the form "join table9 on table1.i_users=table9.i_users and table9.value in (x,y,z)".The problem is that the query is returning duplicate rows; one for each match in table9. I only one one copy of each record.Just moving to "select distinct" doesn't work -- the query is very complex, and it is possible for items to be in the order by that are not in the select (which distinct does not like).I really should rewrite the entire query, but I'm looking for an interim solution. Any ideas on how to match multiple possible rows in the join without returning one *per* match?Thanks-b |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-01-17 : 23:50:02
|
| [code]join (select i_Users, {aggregates of other columns to return} from table9 group by i_Users where value in (x,y,z)) tb9on table1.i_Users = tb9.i_Users[/code]something like that?- Jeff |
 |
|
|
|
|
|
"join on" with IN returning multiple rows