SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 I need to get distinct rows from multi table query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

clawr687
Starting Member

2 Posts

Posted - 03/10/2012 :  17:36:42  Show Profile  Reply with Quote
Hi there,

I am trying to get only the distinct rows foe my query but I'm not too sure how to do that.

The results I am getting are:


147 92 22 1 1 1 1 1 Worker 1
150 92 22 1 1 1 1 1 Worker 1
147 93 25 1 45 62.50 90 110 Worker 1
150 93 25 1 45 62.50 90 110 Worker 1
140 94 25 2 40 60 80 110 Worker 2
141 95 25 3 38 57 76 110 worker 3
148 95 25 3 38 57 76 110 worker 3
149 95 25 3 38 57 76 110 worker 3


The results I want are:

147 93 25 1 45 62.50 90 110 Worker 1
150 93 25 1 45 62.50 90 110 Worker 1
140 94 25 2 40 60 80 110 Worker 2
141 95 25 3 38 57 76 110 worker 3
148 95 25 3 38 57 76 110 worker 3
149 95 25 3 38 57 76 110 worker 3

Column 1 is tbl_lem.lem_ID


This is my Query:
SELECT tbl_rate.rate_ID, tbl_rate.rate_site_IDref, tbl_rate.rate_rank_IDref, tbl_rate.rate_dollar, tbl_rate.rate_ot, tbl_rate.rate_dt, tbl_rate.rate_loa,
tbl_employee.employee_name, tbl_LEM.LEM_date, tbl_LEM.LEM_reg_hrs, tbl_LEM.LEM_ot_hrs, tbl_LEM.LEM_dt_hrs, tbl_LEM.LEM_tt_hrs, tbl_LEM.site_IDREF,
tbl_LEM.LEM_LOA, tbl_LEM.LEM_Expenseinfo, tbl_LEM.LEM_workorder, tbl_LEM.LEM_posted, tbl_LEM.LEM_Expense, tbl_LEM.LEM_tagnumberREF,
tbl_LEM.LEM_reg_rate, tbl_LEM.Lem_ot_rate, tbl_LEM.Lem_tt_rate, tbl_LEM.LEM_dt_rate, tbl_LEM.Lem_loa_rate, tbl_LEM.LEM_ID, tbl_LEM.LEM_equip_days
FROM tbl_rate INNER JOIN
tbl_employee ON tbl_rate.rate_rank_IDref = tbl_employee.employee_rankREF INNER JOIN
tbl_LEM ON tbl_employee.employee_ID = tbl_LEM.employee_IDREF
WHERE (tbl_LEM.site_IDREF = #FORM.site_ID#)
AND (tbl_rate.rate_site_IDref= = #FORM.site_ID#)This line causes me grief
AND (tbl_LEM.LEM_date = <cfqueryparam value="#FORM.LEM_date#" cfsqltype="cf_sql_timestamp">)

The unique column that I need the results by is tbl_lem.lem_ID

Thanks in advance for your help

Edited by - clawr687 on 03/10/2012 18:01:21

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/10/2012 :  23:08:45  Show Profile  Reply with Quote
whats the rule which determines what row to be selected within a worker group? how do you determine the duplicate? please explain with some sample data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000