Join with intermediate table

[modialsi]

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

[teddysnips]

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

[modialsi]

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