Trouble getting right data with select query

[Dresse]

Hello again

I am having some trouble getting the right data out of my database, maybe any of you can help me get the right data.

My Tables:

tblReport
ReportID (pk)
Date

joinTable
ReportID
YardID
JoinID (pk)

tblMachines
MachineID (pk)
Machinename

tblTools
ToolID (pk)
Toolname

ReportMachines
ReportID (pk)
MachineID (pk)

ReportTools
ReportID (pk)
ToolID (pk)

---- And some more tables but these are the most important ones that I have trouble with.

Basically I want to select the tools and machines belonging to each Report made.
When I use this query:

SELECT DISTINCT tblMachines.Machinename, tblTools.Toolname
FROM tblTools INNER JOIN ((tblMachines INNER JOIN (joinTabel INNER JOIN ReportMachines ON joinTabel.JoinID = ReportMachines.ReportID) ON tblMachines.MachineID = ReportMachines.MachineID) INNER JOIN ReportTools ON joinTable.JoinID = ReportTools.ReportID) ON tblTools.ToolID = ReportTools.ToolID
WHERE (((joinTabel.JoinID)=2));

The result is:

Machinename - Toolname

Machine1 - Tool1
Machine1 - Tool3
Machine2 - Tool1
Machine2 - Tool3

The result I expected would be:

Machinename - Toolname

Machine1 - Tool1
Machine2 - Tool3

So my database structure is bad or my tables are not correctly joined? Any help is very much appreciated because I am kinda lost in this one.

Thanks for reading already!

[manxman777]

could you post the few records from each table that lead to the results that you are getting?

[NeoPa]

I would ask how it can makes sense to use :

ON joinTabel.JoinID = ReportMachines.ReportID

or even :

ON joinTable.JoinID = ReportTools.ReportID

[Dresse]

Some records from each table:

tblReport
ReportID (pk) - Date
1 - 7/08/2010
2 - 7/08/2010

joinTable
ReportID - YardID - JoinID (pk)
1 - 1 - 1
2 - 1 - 2

tblMachines
MachineID (pk) - Machinename
1 - Machine1
2 - Machine2
3 - Machine3

tblTools
ToolID (pk) - Toolname
1 - Tool1
2 - Tool2
3 - Tool3


ReportMachines
ReportID (pk) - MachineID (pk)
1 - 1
1 - 2
1 - 3
2 - 1
2 - 2

ReportTools
ReportID (pk) - ToolID (pk)
1 - 1
1 - 2
1 - 3
2 - 1
2 - 3

-------------------------
The above is some testdata, the joinTable is the most important table since i'm want to include it in my WHERE clause.
For example i'm trying to select the machinename and toolname where the joinID equals 1.

@NeoPa

I need to join those tables because I need to know what machines or tools are required for each Report. In ReportMachines or ReportTools each ReportID has a matching MachineID, with that MachineID I can find the right machinename in the other table. Then I try to join the ReportID on the joinTable so I know which Yard and Report it belongs to. I hope my explanation is sufficient enough, its not easy to explain.

Thank you both for reading and I hope you can help me trough this.
I made a small mistake in primary keys in my first post so i updated this now

[manxman777]

Dresse:

If I follow this manually, starting with the requirement that JoinID=2 .....

That means that the ReportID will be 2

That means that the Machines in use are 1 and 2
and
The Tools in use are 1 and 3.

Now, the database has no way of knowing which tool goes with which machine, so it mixes them in all possible ways.

Machine 1 with tools 1 and 3; Machine 2 also with tools 1 and 3. Thus the 4 "records" of your original query. You say there should only be 2 rows in the result, but I do not see how that is possible in the current structure.

If there should be a relationship between Machines and Tools, then you need to state it in the database's relationships.

Perhaps you should forget what you already have and start again. Start by stating (in words) what things exist in the real world that your database must track, and how all these "things" are related to each other, and then we can redesign the tables.

I will watch for your reply.

Richard

[NeoPa]

A point I would re-iterate from my earlier post (although I may not have been as clear as I should have there), as an addition to Richard's very useful post, is that naming objects (tables, fields, etc) can be very important in helping you work things out in your head. Having fields called JoinID & ReportID that are supposed to hold linking data is misleading. It makes it complicated for us to read your SQL, but if anything it's more important that it also makes it much harder for you to follow your own SQL and work with it in any meaningful way.

If the item is a Report ID, then call it that wherever it's found. Join ID is pretty ambiguous. It tells you only that it's used for joining. In a database that's not likely to be unique, so is just noise, leaving you with nothing to indicate what's in the field. Confusion all round.

[Dresse]

I have carefully ready your remarks and adjusted my database and fieldnames accordingly. Thank you very much for the eyeopener. My database is finally working as I was hoping for!

Regards

Dresse

[NeoPa]

Congratulations indeed then. My points were merely tools to assist, and I'm very glad you found them helpful, but you had to push on from there and fix your SQL using those tools, which is a result :)