Re: DISTINCT and LEFT JOIN problem
From: Roji. P. Thomas (lazydragon_at_nowhere.com)
Date: 10/14/04
- Next message: Adi: "Re: Stored Procedures"
- Previous message: Roji. P. Thomas: "Re: DISTINCT and LEFT JOIN problem"
- In reply to: Kees Hoogakker: "Re: DISTINCT and LEFT JOIN problem"
- Next in thread: Roji. P. Thomas: "Re: DISTINCT and LEFT JOIN problem"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 14 Oct 2004 12:01:18 +0530
Kees,
You said in your first post that
"The query results in one row.
If I remove the DISTINCT I get 50 (different) rows!"
And you confirmed in your second post that
"All 50 rows are different..."
SELECT DISTINCT should all rows eliminating the duplicates.
So if your narrative is true, thats a bug. But I dont think thats the
case.
You might be doing something wrong. If you can post enough
information to reproduce the issue you are facing, we are glad to help.
-- Roji. P. Thomas Net Asset Management https://www.netassetmanagement.com "Kees Hoogakker" <nospam.keha@vdc-it.nl> wrote in message news:10mqi3q4hs3ed89@corp.supernews.com... > Tried to keep it small (just the 3 tables, just a few rows, just the > columns > needed, etc.) but then the problem is gone... > What else to do? Guess I just add the eppRate in the SELECT clause to > work-around. > > Thanks > > "Kees Hoogakker" <nospam.keha@vdc-it.nl> wrote in message > news:10mq9h4ared32d1@corp.supernews.com... >> Guess so, but in what way/format do you want it (never did this before). >> >> "Roji. P. Thomas" <lazydragon@nowhere.com> wrote in message >> news:eioTpKSsEHA.160@TK2MSFTNGP11.phx.gbl... >> > Can you post a repro? >> > >> > >> > -- >> > Roji. P. Thomas >> > Net Asset Management >> > https://www.netassetmanagement.com >> > >> > >> > "Kees Hoogakker" <nospam.keha@vdc-it.nl> wrote in message >> > news:10mq8mffprmtad2@corp.supernews.com... >> > > All 50 rows are different... >> > > >> > > "Wayne Snyder" <wayne.nospam.snyder@mariner-usa.com> wrote in message >> > > news:%235oSs5RsEHA.2712@TK2MSFTNGP10.phx.gbl... >> > >> Without seeing the actual result set, >> > >> >> > >> the DISTINCT keyword applies to the entire row ( not just the column >> that >> > > is >> > >> beside the keyword)... >> > >> >> > >> So I would assume that all 50 rows are the same, and when you > distinct >> > > them, >> > >> all of the dupes are removed from the result set... >> > >> >> > >> >> > >> -- >> > >> Wayne Snyder, MCDBA, SQL Server MVP >> > >> Mariner, Charlotte, NC >> > >> www.mariner-usa.com >> > >> (Please respond only to the newsgroups.) >> > >> >> > >> I support the Professional Association of SQL Server (PASS) and it's >> > >> community of SQL Server professionals. >> > >> www.sqlpass.org >> > >> >> > >> "Kees Hoogakker" <nospam.keha@vdc-it.nl> wrote in message >> > >> news:10mq4d6kgs88842@corp.supernews.com... >> > >> > Hi, >> > >> > >> > >> > Got SQL Server 2000 Sp3a and the following query on 3 tables: >> > >> > ____________ >> > >> > SELECT DISTINCT resResourceID, resDateIn, resDateOut, > empEmployeeID, >> > >> > empLastName >> > >> > FROM Employee >> > >> > INNER JOIN Resource ON Employee.empEmployeeID = >> Resource.resEmployeeID >> > >> > INNER JOIN Department ON Resource.resDepartmentID = >> > >> > Department.depDepartmentID >> > >> > LEFT JOIN ResourcePerProject ON Resource.resResourceID = >> > >> > ResourcePerProject.eppResourceID >> > >> > WHERE empSupervisor='user123' ORDER BY empEmployeeID >> > >> > _____________ >> > >> > The query results in one row. >> > >> > If I remove the DISTINCT I get 50 (different) rows! >> > >> > Is this a SQL Server 2000 bug, or do I miss the point here? >> > >> > >> > >> > Furthermore if I add a column of the table 'ResourcePerProject' > (the >> > > first >> > >> > query doesn't select any 'ResourcePerProject' columns), having all >> the >> > >> same >> > >> > contents I get 50 rows. So: >> > >> > _____________ >> > >> > SELECT DISTINCT resResourceID, resDateIn, eppRate, resDateOut, >> > >> > empEmployeeID, empLastName >> > >> > FROM Employee >> > >> > INNER JOIN Resource ON Employee.empEmployeeID = >> Resource.resEmployeeID >> > >> > LEFT JOIN ResourcePerProject ON Resource.resResourceID = >> > >> > ResourcePerProject.eppResourceID >> > >> > WHERE empSupervisor='user123' ORDER BY empEmployeeID >> > >> > ____________ >> > >> > works fine (I added eppRate in the select clause, which is zero >> > >> > for >> all >> > >> > rows). >> > >> > >> > >> > A workaround like: >> > >> > ______________ >> > >> > SELECT DISTINCT * FROM >> > >> > (SELECT resResourceID, resDateIn, resDateOut, empEmployeeID, >> > > empLastName >> > >> > FROM Employee >> > >> > INNER JOIN Resource ON Employee.empEmployeeID = >> Resource.resEmployeeID >> > >> > LEFT JOIN ResourcePerProject ON Resource.resResourceID = >> > >> > ResourcePerProject.eppResourceID >> > >> > WHERE empSupervisor='user123') As Tbl >> > >> > ORDER BY empEmployeeID >> > >> > ______________ >> > >> > doesn't work either: I just get one result (but another row). >> > >> > >> > >> > Please help, >> > >> > Thanks in advance, >> > >> > Keha >> > >> > >> > >> > >> > >> >> > >> >> > > >> > > >> > >> > >> >> > >
- Next message: Adi: "Re: Stored Procedures"
- Previous message: Roji. P. Thomas: "Re: DISTINCT and LEFT JOIN problem"
- In reply to: Kees Hoogakker: "Re: DISTINCT and LEFT JOIN problem"
- Next in thread: Roji. P. Thomas: "Re: DISTINCT and LEFT JOIN problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
