Consulting

Results 1 to 11 of 11

Thread: Solved: SQL Server, SELECT DISTINCT [joined tables]

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

    Solved: 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.
    [vba]
    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|'));

    [/vba]

    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.

  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
    [VBA]
    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|';
    [/VBA]

  5. #5
    VBAX Tutor
    Joined
    Nov 2007
    Posts
    257
    Location
    Quote Originally Posted by Tommy
    Stan,
    Just for grins see if this works
    [vba]
    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|';
    [/vba]
    Here's another version
    [vba]
    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|';[/vba]
    If neither Tommy's attempt or this works, how about posting the table and some sample data. Anyway let's hope it works.

  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

    [vba]
    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|'
    [/vba]

    as an example..

    which table is the source of the duplicate rows?
    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
  •