SQL Server, SELECT DISTINCT [joined tables]

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

Quote:

---

Originally Posted by stanl

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.

Quote:

---

Originally Posted by orange

Please show us the SQL you are using.

---

Code:

---

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.

Stan,
Just for grins see if this works

Code:

---

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|';

---

Quote:

---

Originally Posted by Tommy

Stan,
Just for grins see if this works

Code:

---

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

Code:

---

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.

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:

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

In my simple sample I am getting 1 tech with 1 order from the original SQL. So now I am confused.

Quote:

---

Originally Posted by stanl

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?

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.

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

Code:

---

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?