Duplicate rows returned

[Peekay]

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:

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

[Zvoni]

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

[Peekay]

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

PK

[Zvoni]

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)

[Peekay]

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

[Zvoni]

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:

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

[Peekay]

Zvoni,

I have tested that and it also produces quadruple rows.

PK

[Zvoni]

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

[Peekay]

ZZvoni,

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

Attachment 182784

PK

[Zvoni]

Invalid Attachment. zip it first

[Peekay]

Attachment 182785

[Zvoni]

Nope. No idea what you're doing wrong

[DataMiser]

Here is a sample of a query I was just working with using two left joins and returns correctly

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.

[Peekay]

DataMiser,

Thanks you, I will try it.

PK

[DataMiser]

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?

[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

[Zvoni]

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!

[Peekay]

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

[Shaggy Hiker]

Those attachments are broken, for us. This is a known problem with attachments, though I'm not familiar with what the solution is.