Click here to Skip to main content
15,662,459 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi all,

I have two tables one of the table columns like
Id                        Total stops   Total Emp
100816080630010P   5                     6

Id                        Stopnumbers   EmpId
100816080630010P     1     BACS136
100816080630010P     3     EmpHydTest118
100816080630010P     2     EmpHydTest13
100816080630010P     2     EmpHydTest19
100816080630010P     4     EmpHydTest52
100816080630010P     1     hdtest177

Form above tbl_trips total employees are 6 and in the tbl_tripdet empid count is 6 and stopnumbers should not repeat like this and employees count should be same. If the count is less than or grater than then those details should be get. It should be like 1,2,3,4,5,6. If the case is like as show above stopnumbers repeating then we should get those records.

I tried like this:
SELECT fld_tripid FROM tbl_trips tb
WHERE (fld_totalemps != (SELECT COUNT(fld_EMPID ) FROM tbl_tripdet
WHERE fld_tripid = tb.fld_tripid) OR
 ((tb.fld_TotalEmps * (tb.fld_TotalEmps+1))/2) !=
(SELECT SUM(fld_StopNumber) FROM tbl_tripdet
WHERE fld_tripid = tb.fld_tripid))
AND (SELECT COUNT(fld_EMPID) from tbl_TripDet where fld_TripID = tb.fld_TripID) >0

When I used this query it's taking time to run.

Thanks & Regards,
Updated 10-Oct-10 21:27pm

1 solution

SELECT COUNT(fld_EMPID ), * FROM tbl_tripdet
group by COUNT(fld_EMPID )
Having COUNT(fld_EMPID ) > 1

that will get your duplicates.
for more; google 'group by having SQL'
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900