PDA

View Full Version : [SOLVED] SQL Server, SELECT DISTINCT [joined tables]



stanl
02-06-2008, 05:41 PM
I have Googled posts about this issues have have not found a satisfying answer.

Assume 3 tables T1...T3

relations
T1 -----> T2 [by Primary Key]
T2 -----> T3 [by foreign key]

running a SELECT DISTINCT query on T1 [primary key] but getting fields from T2, T3 produces duplicates rows with T1

Stan

orange
02-06-2008, 07:14 PM
I have Googled posts about this issues have have not found a satisfying answer.

Assume 3 tables T1...T3

relations
T1 -----> T2 [by Primary Key]
T2 -----> T3 [by foreign key]

running a SELECT DISTINCT query on T1 [primary key] but getting fields from T2, T3 produces duplicates rows with T1

Stan
Please show us the SQL you are using.

stanl
02-10-2008, 12:32 AM
Please show us the SQL you are using.


SELECT DISTINCT dbo.work.ext_document_no, dbo.work.entry_date, dbo.employee.first_name AS TechF, dbo.employee.last_name AS TechL
FROM dbo.work INNER JOIN (dbo.work_act INNER JOIN dbo.employee ON dbo.work_act.technician_id = dbo.employee.emp_id) ON dbo.work.work_no = dbo.work_act.work_no
WHERE (((dbo.work.ext_document_no)='|wo|'));

Note: '|wo|' is used to substitute a 15 digit workorder - this query is run against 12-15,000 rows at a time. I didn't create the table structure, I inherited it.

Tommy
02-14-2008, 11:31 AM
Stan,
Just for grins see if this works


SELECT DISTINCT wk.ext_document_no, wk.entry_date, e.first_name As TechF, e.last_name As TechL
FROM dbo.work wk
INNER JOIN dbo.work_act ac ON wk.work_no = ac.work_no
INNER JOIN dbo.employee e ON ac.technician_id = e.emp_id
WHERE wk.ext_document_no= '|wo|';

orange
02-14-2008, 12:12 PM
Stan,
Just for grins see if this works


SELECT DISTINCT wk.ext_document_no, wk.entry_date, e.first_name As TechF, e.last_name As TechL
FROM dbo.work wk
INNER JOIN dbo.work_act ac ON wk.work_no = ac.work_no
INNER JOIN dbo.employee e ON ac.technician_id = e.emp_id
WHERE wk.ext_document_no= '|wo|';

Here's another version


SELECT DISTINCT dbo.work.ext_document_no
, dbo.work.entry_date
, dbo.employee.first_name As TechF
, dbo.employee.last_name As TechL
FROM dbo.work
,dbo.employee
,dbo.work_act
WHERE
dbo.work_act.technician_id = dbo.employee.emp_id AND
dbo.work.work_no = dbo.work_act.work_no AND
dbo.work.ext_document_no)= '|wo|';
If neither Tommy's attempt or this works, how about posting the table and some sample data. Anyway let's hope it works.

stanl
02-16-2008, 08:16 AM
No, neither works. This is live stuff. The point was to get the Technician for each distinct workorder. I plan-B'd it with a While loop as a simple query appears to return duplicates no matter what.

Table structure was something I inherited... too bad the persons who created it were working from "SQL For Dummies" :banghead:

Tommy
02-16-2008, 08:46 AM
Since you have data could you set-up a small sample? Otherwise I will have to make it up and my SQL for dummies can't handle imagination. LOL

Tommy
02-16-2008, 09:11 AM
In my simple sample I am getting 1 tech with 1 order from the original SQL. So now I am confused.

orange
02-16-2008, 09:33 AM
No, neither works. This is live stuff. The point was to get the Technician for each distinct workorder. I plan-B'd it with a While loop as a simple query appears to return duplicates no matter what.

Table structure was something I inherited... too bad the persons who created it were working from "SQL For Dummies" :banghead:

Stan
What exactly are you getting back?
Can you tell us what's in the "duplicate" rows?

stanl
02-17-2008, 09:48 AM
I'm about to mark this thread as solved, unfortunately it cannot be solved by a simple query. If there were a direct relationship between the original workorder table and the employee table, then there would be no problem.

Having the wo_act table as an intermediary relates to the status of a workorder. It gets very confusing, but so is a real business sometimes :banghead: which causes a duplication of the original workorder. This is one of many issues the company[which purchased the db structure/software for 200k] has with tracking workorders and RMA's.

I am sure if you replicated the original pseudo-table structure I outlined there would be no problem, as you would assume and permit unique values throughout. As I mentioned, the situation is under control via a WHILE loop and has been corrected for the past 6 months of data. Going forward the code will run daily and hence about as fast as the SELECT/UPDATE query.

XLGibbs
03-04-2008, 06:03 PM
Stan..

DISTINCT keyword will return unique ROWS from the entire Select statement

You use derived tables in SQL to modify the tables you are joining


SELECT DISTINCT dbo.work.ext_document_no,
dbo.work.entry_date,
dbo.employee.first_name As TechF,
dbo.employee.last_name As TechL
FROM dbo.work work
INNER JOIN (Select Distinct work_no,
technician_id
FROM dbo.Work_Act ) account ON work.work_no = account.work_no
INNER JOIN (Select DISTINCT technician_id,
first_name as TechF,
last_name as TechL
FROM dbo.employee) employee on account.technician_id = empoyee.technician_id
WHERE work.ext_document_no = '|wo|'


as an example..

which table is the source of the duplicate rows?