June 21, 2007 at 8:33 am
select
Distinct (e.empID),empName,lc.CountyName
from Indicator I, emp e
inner join OfficeCounty oc on oc.empid=e.empid
inner join LookupCounty lc on lc.countyid=oc.countyid
where e.jobid = I.IndicatorID
and I.UserRoleID >= 9)
I am looking for distinct values in the above query but the rows are repeated, pls let me know where am I worng.
getting 2800 rows where I have to get only 250 rows
June 21, 2007 at 8:59 am
Is there any reason why you do not have any join conditions between Indicator and emp tables? As it stands, it will produce a cartesean product (cross join) between the two tables and might be why you are getting more rows than you expect.
J
June 21, 2007 at 9:05 am
Actually there is condition in where clause like
e.jobID = I.IndicatorID
but anyways how can i get rid of the cartesian product and get only distinct values
June 21, 2007 at 9:16 am
First, don't mix old-style and ANSI join syntax. Rewrite something like this:
select Distinct (e.empID),empName,lc.CountyName
from Indicator I
inner join emp e on e.jobid = I.IndicatorID
inner join OfficeCounty oc on oc.empid=e.empid
inner join LookupCounty lc on lc.countyid=oc.countyid
where I.UserRoleID >= 9
Second, you say you only want distinct values: does that mean you only want each value of e.empID to appear once? If so, how are you going to decide which row to select?
John
June 21, 2007 at 10:48 am
but still didnt work...
June 21, 2007 at 11:01 am
when I have DISTINCT for empid y does empid duplicate in this scenariao?
June 21, 2007 at 11:46 am
You're probably getting "Distinct" combinations of your 3 select fields
If your data has 2 identical empID's with the same empName and CountyName, you'd get 1 row back
If your data has 1 empID with multiple combinations of empName and/or CountyName, you'd get multiple rows back
If this latter example is the case, you'd probably need to tweak your WHERE clause a bit (maybe use a subquery)
JB
June 21, 2007 at 12:49 pm
select
Distinct (e.empID),empName
from Indicator I, emp e
where e.jobid = I.IndicatorID
and I.UserRoleID >= 9)
actually for the above query how can i get countyname in my select list from the tablles
1.Lookupcounty(countyid,countyname)
2.Lookupemp(empid,countyid)
June 21, 2007 at 3:07 pm
Having no concrete idea of what your table structure looks like...does this get close?
SELECT
DISTINCT e.empID, e.empName, c.countyName
FROM emp e
JOIN indicator i ON e.empID = i.indicatorID
JOIN lookUpEmp l ON e.empID = l.empID
JOIN lookUpCounty c ON l.countyID = c.countyID
WHERE e.jobID = i.indicatorID AND i.userRoleID >=9
JB
June 22, 2007 at 3:01 am
SELECT e.empID, empName, lc.CountyName
FROM emp e
JOIN (
SELECT oc.empid
-- You have to decide which county you want.
-- Just taking the lowest ID here
,MIN(oc.countyid) AS countyid
FROM OfficeCounty oc
GROUP BY oc.empid
) D
ON e.empid = D.empid
JOIN LookupCounty lc
ON D.countyid = lc.countyid
-- As not selecting from Indicator, use EXISTs to stop the possibility of multiple rows
WHERE EXISTS (
SELECT *
FROM Indicator I
WHERE I.IndicatorID = e.jobid
AND I.UserRoleID >= 9
)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply