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 |
|
karuna
Aged Yak Warrior
582 Posts |
 Posted - 2005-04-13 : 14:53:34
|
Whats wrong in this? select distinct a.reselleraccountno,a.resellername from transformationtemptable a,reseller b where a.validrecord = 1 and a.reselleraccountno <> b.reselleraccountno) It fetches all records from table a where the validrecord is 1 and fetches the records where a.reselleraccountno = b.reselleraccountno  BTW table a has multiple records with same reselleraccountno and resellername and both are of datatype int.Karunakaran___________NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER... |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-04-13 : 15:01:26
|
sample data and desired results would be nice but...select distinct a.reselleraccountno,a.resellername from transformationtemptable awhere a.validrecord = 1 and not exists (select * from reseller b where a.reselleraccountno = b.reselleraccountno)Go with the flow & have fun! Else fight the flow  |
 |
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-04-13 : 15:29:51
|
spirit1,That works fine. Can I write the above like this?select distinct a.reselleraccountno,a.resellername from transformationtemptable awhere a.validrecord = 1 and not exists (select distinct reselleraccountno from reseller b where a.reselleraccountno = b.reselleraccountno) BTW why my query is not giving the desired result? whats wrong in it?Karunakaran___________NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER... |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-04-13 : 15:58:33
|
| Listen Very Carefully...to help us help you we need more infor.We need.1. The Table DDL for all tables in the problemCREATE TABLE myTable(Col1 int, Col2 int, ect2. Sample Data to put in the TableINSERT INTO myTable99(Col1,Col2, ectSELECT 1,2, ect UNION ALLSELECT 1,2, ect UNION ALLect3. Any DML that you've attempted (which you did, or what the requirement is)4. The expect ResultsUnless you post this, it'll only be a guessing game.Graz Can we get something like this posted somewhere...especially at AskSQLTeam... Brett8-) |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-04-14 : 00:06:26
|
This part of the query seems contrary to your statement of "fetches the records where a.reselleraccountno = b.reselleraccountno". It looks like you are getting the rows where they are not equal.a.reselleraccountno <> b.reselleraccountno quote:
Originally posted by karuna
Whats wrong in this?select distinct a.reselleraccountno,a.resellername from transformationtemptable a,reseller b where a.validrecord = 1 and a.reselleraccountno <> b.reselleraccountno) It fetches all records from table a where the validrecord is 1 and fetches the records where a.reselleraccountno = b.reselleraccountno BTW table a has multiple records with same reselleraccountno and resellername and both are of datatype int.Karunakaran___________NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...
CODO ERGO SUM |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-04-14 : 01:14:47
|
quote:
Originally posted by Michael Valentine Jones
It looks like you are getting the rows where they are not equal.a.reselleraccountno <> b.reselleraccountno CODO ERGO SUM
Well, thats what I wanted. But that query gave me results irrespective of a.reselleraccountno <> b.reselleraccountno condition. Sorry that the post was not clear on what I was saying.Karunakaran___________NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER... |
|
|
|
|
|
|
|
|
Whats wrong in this?