Re: DISTINCT and LEFT JOIN problem

From: Roji. P. Thomas (lazydragon_at_nowhere.com)
Date: 10/14/04 

Date: Thu, 14 Oct 2004 11:55:46 +0530

http://www.aspfaq.com/etiquette.asp?id=5006 

-- 
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"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
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Relevant Pages

  • Re: Dynamic table name in query
    ... "Roji. ... P. Thomas" wrote in message ... >> Dynamic SQL, you could just explain your position or at least write the ... >>> You are trying to write a procedure without any cohesion at all. ...
    (microsoft.public.sqlserver.programming)
  • Re: searching with varying parameters
    ... "Roji. ... P. Thomas" wrote in message ... > Net Asset Management ... >> body, Search by attachment name, search if message has attachments). ...
    (microsoft.public.sqlserver.programming)
  • Re: Output parameters
    ... > Set CmdParams = Nothing ... > "Roji. ... P. Thomas" wrote in message ... >> Net Asset Management ...
    (microsoft.public.sqlserver.programming)
  • Re: Structure Help
    ... "Drew" wrote in message ... > "Roji. ... P. Thomas" wrote in message ... CharacteristicsInPeople - Again a list ...
    (microsoft.public.sqlserver.programming)
Loading