Matching Problem

[DawnTreader]

Hello

i have a querying problem that i need to solve.

i have to sets of parts. Valves and Repair Kits.

there is a manufacture part number for both that i can match on.

the problem is that i know that there are valves in my list that dont have
repair kits. and i know that there are repair kits that dont have matching
valves.

i want to create a list that shows all of the matches as well as both the
valves that dont have repair kits in my list and repair kits that dont have
valves.

both are stored in one table and i have 2 seperate queries that show all the
valves that have repair kits or not, and i have another query that shows all
the repair kits that have valves or not.

how do i bring the 2 together based on the manufacturer part number?

 

[Ken Sheridan]

Lets assume the table is called Parts and has columns PartNumber, PartName,
PartType (with possible values 'Valve' or 'Repair Kit'), then you can use a
UNION ALL operation of three parts; the first joining two instances of the
table and returning all valves with repair kits, the second and third, by
means of subqueries, returning valves without repair kits and repair kits
without valves. In the first the Valve and Repair Kit columns in the result
table would both have values, in the second and third only one would have a
valve or repair kit name, the other something like 'None'.

The query would go like this:

SELECT P1.PartNumber, P1.PartName AS "Valve",
P2.PartName AS "Repair Kit"
FROM Parts AS P1, Parts AS P2
WHERE P1.PartNumber = P2.PartNumber
AND P1.PartType = "Valve"
AND P2.PartType = "Repair Kit"
UNION ALL
SELECT P1.PartNumber, P1.PartName, "None"
FROM Parts AS P1
WHERE PartType = "Valve"
AND NOT EXISTS
(SELECT *
FROM Parts AS P2
WHERE P2.PartNumber = P1.PartNumber
AND P2.PartType = "Repair Kit")
UNION ALL
SELECT P1.PartNumber, "None", P1.PartName
FROM Parts AS P1
WHERE PartType = "Repair Kit"
AND NOT EXISTS
(SELECT *
FROM Parts AS P2
WHERE P2.PartNumber = P1.PartNumber
AND P2.PartType = "Valve");

Ken Sheridan
Stafford, England

 

[DawnTreader]

Hello

i have 1 query that gives all the valves regardless of whether it has repair
kits or not. i have another query that gives all the repair kits regardless
of whether it has a matching valve.

should i union query these queries?

here is the SQL for the valve to repairkits:

SELECT qryHoerbigerValves.ID AS IMWValvePN, qryHoerbigerValves.DESCRIPTION,
qryHoerbigerValves.UnitCost AS [Valve UC], qryHoerbigerValves.MFG_PART_ID AS
ValveMFGPN, qryHoerbigerRepairKits.MFG_PART_ID AS RKMFGPN,
qryHoerbigerRepairKits.ID AS IMWRKPN, qryHoerbigerRepairKits.DESCRIPTION,
qryHoerbigerRepairKits.UnitCost AS [RK UC]
FROM qryHoerbigerRepairKits RIGHT JOIN qryHoerbigerValves ON
qryHoerbigerRepairKits.ValveMatchingID = qryHoerbigerValves.ValveMatchingID;

and here is the repair kits to valves:

SELECT qryHoerbigerValves.ID, qryHoerbigerValves.DESCRIPTION,
qryHoerbigerValves.MFG_NAME, qryHoerbigerValves.MFG_PART_ID,
qryHoerbigerValves.ValveMatchingID, qryHoerbigerRepairKits.ValveMatchingID,
qryHoerbigerRepairKits.MFG_PART_ID, qryHoerbigerRepairKits.MFG_NAME,
qryHoerbigerRepairKits.DESCRIPTION, qryHoerbigerRepairKits.ID
FROM qryHoerbigerRepairKits LEFT JOIN qryHoerbigerValves ON
qryHoerbigerRepairKits.ValveMatchingID = qryHoerbigerValves.ValveMatchingID;

i was thinking that if i did a union query i could use a select distinct to
get all of each query, joining them on the valveamatching ids.

 

[DawnTreader]

HA HA!

thanks

i think i worked it out:

SELECT
qryHoerbigerValves.ID AS [V IMW PN],
qryHoerbigerValves.DESCRIPTION AS [V Desc],
qryHoerbigerValves.UnitCost AS [V UC],
qryHoerbigerValves.MFG_PART_ID AS [V MFG PN],
qryHoerbigerRepairKits.MFG_PART_ID AS [RK MFG PN],
qryHoerbigerRepairKits.ID AS [IMW RK PN],
qryHoerbigerRepairKits.DESCRIPTION AS [RK Desc],
qryHoerbigerRepairKits.UnitCost AS [RK UC]
FROM qryHoerbigerRepairKits RIGHT JOIN qryHoerbigerValves ON
qryHoerbigerRepairKits.ValveMatchingID = qryHoerbigerValves.ValveMatchingID
UNION SELECT
qryHoerbigerValves.ID AS [V IMW PN],
qryHoerbigerValves.DESCRIPTION AS [V Desc],
qryHoerbigerValves.UnitCost AS [V UC],
qryHoerbigerValves.MFG_PART_ID AS [V MFG PN],
qryHoerbigerRepairKits.MFG_PART_ID AS [RK MFG PN],
qryHoerbigerRepairKits.ID AS [IMW RK PN],
qryHoerbigerRepairKits.DESCRIPTION AS [RK Desc],
qryHoerbigerRepairKits.UnitCost AS [RK UC]
FROM qryHoerbigerRepairKits LEFT JOIN qryHoerbigerValves ON
qryHoerbigerRepairKits.ValveMatchingID = qryHoerbigerValves.ValveMatchingID;

Hello

i have 1 query that gives all the valves regardless of whether it has repair
kits or not. i have another query that gives all the repair kits regardless
of whether it has a matching valve.

should i union query these queries?

here is the SQL for the valve to repairkits:

SELECT qryHoerbigerValves.ID AS IMWValvePN, qryHoerbigerValves.DESCRIPTION,
qryHoerbigerValves.UnitCost AS [Valve UC], qryHoerbigerValves.MFG_PART_ID AS
ValveMFGPN, qryHoerbigerRepairKits.MFG_PART_ID AS RKMFGPN,
qryHoerbigerRepairKits.ID AS IMWRKPN, qryHoerbigerRepairKits.DESCRIPTION,
qryHoerbigerRepairKits.UnitCost AS [RK UC]
FROM qryHoerbigerRepairKits RIGHT JOIN qryHoerbigerValves ON
qryHoerbigerRepairKits.ValveMatchingID = qryHoerbigerValves.ValveMatchingID;

and here is the repair kits to valves:

SELECT qryHoerbigerValves.ID, qryHoerbigerValves.DESCRIPTION,
qryHoerbigerValves.MFG_NAME, qryHoerbigerValves.MFG_PART_ID,
qryHoerbigerValves.ValveMatchingID, qryHoerbigerRepairKits.ValveMatchingID,
qryHoerbigerRepairKits.MFG_PART_ID, qryHoerbigerRepairKits.MFG_NAME,
qryHoerbigerRepairKits.DESCRIPTION, qryHoerbigerRepairKits.ID
FROM qryHoerbigerRepairKits LEFT JOIN qryHoerbigerValves ON
qryHoerbigerRepairKits.ValveMatchingID = qryHoerbigerValves.ValveMatchingID;

i was thinking that if i did a union query i could use a select distinct to
get all of each query, joining them on the valveamatching ids.

Lets assume the table is called Parts and has columns PartNumber, PartName,
PartType (with possible values 'Valve' or 'Repair Kit'), then you can use a
UNION ALL operation of three parts; the first joining two instances of the
table and returning all valves with repair kits, the second and third, by
means of subqueries, returning valves without repair kits and repair kits
without valves. In the first the Valve and Repair Kit columns in the result
table would both have values, in the second and third only one would have a
valve or repair kit name, the other something like 'None'.

The query would go like this:

SELECT P1.PartNumber, P1.PartName AS "Valve",
P2.PartName AS "Repair Kit"
FROM Parts AS P1, Parts AS P2
WHERE P1.PartNumber = P2.PartNumber
AND P1.PartType = "Valve"
AND P2.PartType = "Repair Kit"
UNION ALL
SELECT P1.PartNumber, P1.PartName, "None"
FROM Parts AS P1
WHERE PartType = "Valve"
AND NOT EXISTS
(SELECT *
FROM Parts AS P2
WHERE P2.PartNumber = P1.PartNumber
AND P2.PartType = "Repair Kit")
UNION ALL
SELECT P1.PartNumber, "None", P1.PartName
FROM Parts AS P1
WHERE PartType = "Repair Kit"
AND NOT EXISTS
(SELECT *
FROM Parts AS P2
WHERE P2.PartNumber = P1.PartNumber
AND P2.PartType = "Valve");

Ken Sheridan
Stafford, England