I need to get distinct rows from multi table query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

I need to get distinct rows from multi table query

Author

Topic

clawr687
Starting Member

2 Posts

Posted - 2012-03-10 : 17:36:42

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-10 : 23:08:45

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources