By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,073 Members | 1,218 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,073 IT Pros & Developers. It's quick & easy.

Join with intermediate table

P: n/a
Hi,

I have 3 tables: tPart, tMachine and a table linking these 2:
tMachinePart

tPart: ID, ... partcolumns
tMachinePart: machineid, partid
tMachine: ID,... machinecolumns

I would like to make a join like this:
SELECT tPart.*, tMachine.*
FROM tPart INNER JOIN (tMachine INNER JOIN tMachinePart ON tMachine.ID
= tMachinePart.machineID) ON tPart.ID = tMachinePart.partID;

But the result should be that
- only one part is shown, even with multiple machines
- if no machine is linked to a part, the part should be shown, and
machinecolumns should be NULL

Kind regards,

Thomas

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

modialsi wrote:
Hi,

I have 3 tables: tPart, tMachine and a table linking these 2:
tMachinePart

tPart: ID, ... partcolumns
tMachinePart: machineid, partid
tMachine: ID,... machinecolumns

I would like to make a join like this:
SELECT tPart.*, tMachine.*
FROM tPart INNER JOIN (tMachine INNER JOIN tMachinePart ON tMachine.ID
= tMachinePart.machineID) ON tPart.ID = tMachinePart.partID;

But the result should be that
- only one part is shown, even with multiple machines
- if no machine is linked to a part, the part should be shown, and
machinecolumns should be NULL


If you use an INNER JOIN and one of the columns used in the JOIN is
NULL, then that row will not appear in the results. Try using an OUTER
JOIN instead.

Edward

Nov 13 '05 #2

P: n/a
Thank you for your reply.

It is indeed correct that I should use a outer join, something like
this:

SELECT tPart.ID AS partid, tPart.TypeID AS parttypeid, tPart.PN,
tPart.DealerPrice, tPart.Versiondate, tPart.TranslatorID,
tPart.nimupdate, tPart.sellmultiple, Max(tRevision.revisionNumber) AS
partrevisionnumber, tPart.Schems, Count(tMachinePart.machineID) AS
machineqty, tMachine.model, tMachine.typeid AS machinetypeid,
tMachine.serialnumber, tMachine.id AS machineid
FROM [SELECT top 1 tMachine.id,model,typeid,serialnumber FROM
tMachine]. AS tMachine RIGHT JOIN ((tRevision RIGHT JOIN tPart ON
tRevision.partID=tPart.ID) LEFT JOIN tMachinePart ON
tPart.ID=tMachinePart.partID) ON tMachine.ID=tMachinePart.machineID
GROUP BY tPart.ID, tPart.TypeID, tPart.PN, tPart.DealerPrice,
tPart.Versiondate, tPart.TranslatorID, tPart.nimupdate,
tPart.sellmultiple, tPart.Schems, tMachine.model, tMachine.typeid,
tMachine.serialnumber, tMachine.id;
But the second problem remains: how do I get all the parts, but only
one machine for a part.

The reason the query above doesn't work is that I get only the first
machine in the tMachine table, not the correct machine corresponding to
the part.

Any idea on how to solve this?

Thomas

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.