-
Oct 27th, 2021, 05:54 AM
#1
Thread Starter
Fanatic Member
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
-
Oct 27th, 2021, 06:18 AM
#2
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.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
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
-
Oct 27th, 2021, 06:46 AM
#3
Thread Starter
Fanatic Member
Re: Duplicate rows returned
Zvoni,
Left join return nothing.
How do I move it to the Database forum?
PK
-
Oct 27th, 2021, 07:01 AM
#4
Re: Duplicate rows returned
Originally Posted by Peekay
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)
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
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
-
Oct 27th, 2021, 07:11 AM
#5
Thread Starter
Fanatic Member
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
-
Oct 27th, 2021, 07:20 AM
#6
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
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
-
Oct 27th, 2021, 08:08 AM
#7
Thread Starter
Fanatic Member
Re: Duplicate rows returned
Zvoni,
I have tested that and it also produces quadruple rows.
PK
-
Oct 27th, 2021, 08:34 AM
#8
Re: Duplicate rows returned
Originally Posted by Peekay
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
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
-
Oct 28th, 2021, 01:57 AM
#9
Thread Starter
Fanatic Member
Re: Duplicate rows returned
ZZvoni,
I appreciate the work you put into this.
Here are the tables and data.
Attachment 182784
PK
-
Oct 28th, 2021, 02:18 AM
#10
Re: Duplicate rows returned
Invalid Attachment. zip it first
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
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
-
Oct 28th, 2021, 02:21 AM
#11
Thread Starter
Fanatic Member
Re: Duplicate rows returned
-
Oct 28th, 2021, 02:50 AM
#12
Re: Duplicate rows returned
Nope. No idea what you're doing wrong
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
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
-
Nov 2nd, 2021, 12:22 PM
#13
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.
-
Nov 2nd, 2021, 08:59 PM
#14
Thread Starter
Fanatic Member
Re: Duplicate rows returned
DataMiser,
Thanks you, I will try it.
PK
-
Nov 2nd, 2021, 11:15 PM
#15
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?
-
Nov 3rd, 2021, 02:51 AM
#16
Thread Starter
Fanatic Member
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
-
Nov 3rd, 2021, 04:08 AM
#17
Re: Duplicate rows returned
Originally Posted by Peekay
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!
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
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
-
Nov 3rd, 2021, 05:57 AM
#18
Thread Starter
Fanatic Member
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
-
Nov 3rd, 2021, 09:20 AM
#19
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|