Consulting

Results 1 to 11 of 11

Thread: SQL Server, SELECT DISTINCT [joined tables]

  1. #1
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location

    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

  2. #2
    VBAX Tutor
    Joined
    Nov 2007
    Posts
    257
    Location
    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.

  3. #3
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by orange
    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.
    Last edited by Aussiebear; 04-12-2023 at 12:53 AM. Reason: Adjusted the code tags

  4. #4
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    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|';
    Last edited by Aussiebear; 04-12-2023 at 12:53 AM. Reason: Adjusted the code tags

  5. #5
    VBAX Tutor
    Joined
    Nov 2007
    Posts
    257
    Location
    Quote Originally Posted by Tommy
    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.
    Last edited by Aussiebear; 04-12-2023 at 12:54 AM. Reason: Adjusted the code tags

  6. #6
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    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"

  7. #7
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    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

  8. #8
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    In my simple sample I am getting 1 tech with 1 order from the original SQL. So now I am confused.

  9. #9
    VBAX Tutor
    Joined
    Nov 2007
    Posts
    257
    Location
    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"
    Stan
    What exactly are you getting back?
    Can you tell us what's in the "duplicate" rows?

  10. #10
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    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 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.

  11. #11
    VBAX Master XLGibbs's Avatar
    Joined
    Jan 2006
    Location
    state of confusion, but vacation in denial
    Posts
    1,315
    Location
    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?
    Last edited by Aussiebear; 04-12-2023 at 12:55 AM. Reason: Adjusted the code tags
    If you have posted the same question at multiple forums, please read this IMPORTANT INFO.

    Please use the thread tools to mark your thread Solved


    Please review the Knowledge Base
    for samples and solutions , or to submit your own!




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •