Re: DISTINCT and LEFT JOIN problem
From: Kees Hoogakker (nospam.keha_at_vdc-it.nl)
Date: 10/13/04
- Next message: Ian Oldbury: "Re: Cursor for loops"
- Previous message: Lucas Tam: "Re: ORDER BY"
- In reply to: Kees Hoogakker: "Re: DISTINCT and LEFT JOIN problem"
- Next in thread: Roji. P. Thomas: "Re: DISTINCT and LEFT JOIN problem"
- Reply: Roji. P. Thomas: "Re: DISTINCT and LEFT JOIN problem"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 13 Oct 2004 17:23:27 +0200
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: Ian Oldbury: "Re: Cursor for loops"
- Previous message: Lucas Tam: "Re: ORDER BY"
- In reply to: Kees Hoogakker: "Re: DISTINCT and LEFT JOIN problem"
- Next in thread: Roji. P. Thomas: "Re: DISTINCT and LEFT JOIN problem"
- Reply: Roji. P. Thomas: "Re: DISTINCT and LEFT JOIN problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
