Results 1 to 19 of 19

Thread: Duplicate rows returned

  1. #1

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    647

    Duplicate rows returned

    I have to join three tables

    Table StdTaskMaterials with fields ID, ItemName, StdTaskNo
    Table TaskMaterials with fields ID, ItemUnit, TaskNo
    Table Tasks with fields ID, taskname, StdTaskNo

    I need a list of all TaskMaterials which belongs to a certain Task in Tasks with ID=3, and grab the relevant ItemName from the StdTaskMaterials table

    in the following way:

    Code:
    SELECT taskmaterials.id, tasks.taskname, stdtaskmaterials.itemname, taskmaterials.itemunit FROM (taskmaterials LEFT JOIN tasks ON tasks.id=taskmaterials.taskNo) LEFT JOIN stdtaskmaterials ON stdtaskmaterials.stdtaskno=tasks.stdtaskno WHERE tasks.id=3
    There are present 4 StdTaskMaterials which must be bound to the four TaskMaterials, but I get 16 rows returned.

    I have tried DISTINT and GROUP BY, but nothing works right.

    Thanks
    PK

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,799

    Re: Duplicate rows returned

    Some sample Data?
    And:
    That's what i get
    That's what i want

    EDIT: Have you tried INNER JOIN instead of LEFT JOIN?

    EDIT2: This belongs more in the Database-Forum instead of vb6
    Last edited by Zvoni; Oct 27th, 2021 at 06:23 AM.
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    647

    Re: Duplicate rows returned

    Zvoni,
    Left join return nothing.
    How do I move it to the Database forum?

    PK

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,799

    Re: Duplicate rows returned

    Quote Originally Posted by Peekay View Post
    How do I move it to the Database forum?

    PK
    You don't. You have to wait for the moderators to move it

    EDIT: LEFT JOIN returns nothing? Then i need sample data (upload as zipped csv)
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    647

    Re: Duplicate rows returned

    Zvoni,

    Sorry. INNER JOIN produces no rows
    I have simplified my problem by taking simplified tables and simplified fields and have no data.
    The actual case is more elaborate but the problem the same. The actual case will just confuse the issue.

    I have solved the problem, but it is not an elegant solution:
    I count the number of entries in the TaskMaterials table and then I use: SELECT TOP (number of entries) ...

    PK

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,799

    Re: Duplicate rows returned

    Having looked at your issue again:
    put "tasks" in the FROM-clause, since your primary goal is to see a task (ID=3) and its associated Materials
    therefor: put TaskMaterials to the right of the LEFT JOIN
    Untested:
    Code:
    SELECT 
    taskmaterials.id, 
    tasks.taskname, 
    stdtaskmaterials.itemname, 
    taskmaterials.itemunit 
    FROM 
    tasks 
    LEFT JOIN 
    taskmaterials 
    ON 
    tasks.id=taskmaterials.taskNo 
    LEFT JOIN 
    stdtaskmaterials 
    ON 
    stdtaskmaterials.stdtaskno=tasks.stdtaskno 
    WHERE tasks.id=3
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    647

    Re: Duplicate rows returned

    Zvoni,

    I have tested that and it also produces quadruple rows.

    PK

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,799

    Re: Duplicate rows returned

    Quote Originally Posted by Peekay View Post
    Zvoni,

    I have tested that and it also produces quadruple rows.

    PK
    Then provide some sample data
    Pretty sure it's a simple thing with SQL
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  9. #9

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    647

    Re: Duplicate rows returned

    ZZvoni,

    I appreciate the work you put into this.
    Here are the tables and data.

    Attachment 182784

    PK

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,799

    Re: Duplicate rows returned

    Invalid Attachment. zip it first
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  11. #11

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    647

    Re: Duplicate rows returned


  12. #12
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,799

    Re: Duplicate rows returned

    Nope. No idea what you're doing wrong
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  13. #13
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,876

    Re: Duplicate rows returned

    Here is a sample of a query I was just working with using two left joins and returns correctly
    Code:
    select * from
    (tblspaces left join tblowners on tblspaces.ownerid=tblowners.ownerid)
    left join tblautos on tblautos.ownerid=tblspaces.ownerid
    where lotid='A13'order by spaceid
    It has been a while since I initially wrote it but using the () on the first join seems to have been key to getting the results I wanted in this case.
    Last edited by DataMiser; Nov 2nd, 2021 at 12:25 PM.

  14. #14

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    647

    Re: Duplicate rows returned

    DataMiser,

    Thanks you, I will try it.

    PK

  15. #15
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,876

    Re: Duplicate rows returned

    The order your table names appear in that statement is pretty important as well.
    In the case of my sample statement I needed it to show every space, it also needs to show which owner (if any) are linked to each space and it needs to show each car( if any) the owner has registered.

    so if a owner has 1 space and 1 car it shows 1 row. If the owner has 2 spaces and 2 cars it shows 2 rows for each space owned so 4 total.
    If the owner had 4 spaces and 4 cars then it would show 16 rows but that would be the desired output in this case not sure it applies to your case.

    If you put up a sample data file I'm sure someone can point you in the right direction.
    What db are you using anyway?

  16. #16

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    647

    Re: Duplicate rows returned

    DataMiser,

    Your example is apt, but in my case the cars can only park in their one specific allocated space, so I only need four lines with the four spaces in your case.
    I have published my tables and data in post #11.

    Thanks
    PK

  17. #17
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,799

    Re: Duplicate rows returned

    Quote Originally Posted by Peekay View Post
    DataMiser,

    Your example is apt, but in my case the cars can only park in their one specific allocated space, so I only need four lines with the four spaces in your case.
    I have published my tables and data in post #11.

    Thanks
    PK
    To stay in DM's terminology:
    You have an Owner, who can own none, one or more cars
    You have an Owner, who can rent none, one or more spaces
    If the Owner owns one or more cars, each car can only be allotted one space, and that one space can only be allotted to one car

    Looks like a Triangle-relationship
    Owner - Car = 1:m
    Owner - Space = 1:m
    Space - Car = 1:1

    A "1:1"-relation implies a INNER JOIN!
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  18. #18

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    647

    Re: Duplicate rows returned

    Zvoni,

    I have, say, 4 spaces allocated to four different cars and each car can only park in its allocated space.
    However, I have solved the problem in another manner joining spaces directly to its ID =CarID.
    I think my problem stemmed from a sloppy table structure.

    PK

  19. #19
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    36,908

    Re: Duplicate rows returned

    Those attachments are broken, for us. This is a known problem with attachments, though I'm not familiar with what the solution is.
    My usual boring signature: Nothing

Posting Permissions

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



Click Here to Expand Forum to Full Width