Mysql query / subquery
I am trying to reconcile an existing database and system with the data we now receive from the manufacturer. Its format cannot / cannot change. This is a table with product numbers (item_id) from a single manufacturer that cross-reference multiple manufacturers (oem_code).
Table: xref
item_id | oem_code
A | 123
A | 234
B | 234
B | 345
C | 456
Table: parts (only showing the corresponding column partNum)
partNum
S.A
S.B
S.C
123
234
345
456
Both xref columns contain the part numbers in the parts table. I need to enter a part number ($fielddata)
and then extract all data from parts where parts.partNum = $fielddata
, as well as all cross reference numbers from xref that match parts.partNum
. Unfortunately, manufacturer part numbers have S
. preceding them in our system, but not in the table they send. Our system will not recognize them without S
.
MySQL query
SELECT *
FROM parts
WHERE partNum
IN (
SELECT oem_code
FROM xref
WHERE item_id = (
SELECT item_id
FROM xref
WHERE oem_code = '$fielddata'
)
)
123
returns all data from parts where parts.partNum = 123, 234 (also requires SA) 234
errors, # 1242 - Subquery returns more than 1 row (needs 234, SA, SB) A
returns nothing (needs 123, 234, SA) S.A
also doesn't returns nothing (needs 123, 234, SA)
I understand the limitations of my sql statement and subquery and why I am not getting the full result I want, but I don’t know how to extend it to get what I want. I was glad to get how much I did it. When I try to expand on it, I get unwanted results or errors, so I am pretty much at the end of my options.
I also know that running subqueries are ineffective for large databases. So I'm ready to rewrite sql or change what I have so far. xref has >27k
rows and parts has >550k
rows with 10 columns (in case anyone cares).
Any help would be greatly appreciated.
source to share
Query using exists
SELECT *
FROM parts
WHERE EXISTS (
SELECT 1 FROM xref x1
JOIN xref x2 ON x1.item_id = x2.item_id
WHERE (x2.oem_code = '$fielddata' OR x2.item_id = RIGHT('$fielddata',1))
AND (x1.oem_code = partNum OR CONCAT('S.',x1.item_id) = partNum)
)
Update
It might be faster to join the view for a given one $fielddata
. This is essentially @JohnRuddell's query, but modified to use a derived table.
SELECT p.* FROM parts p
JOIN (
SELECT x.oem_code partNum
FROM xref x
JOIN xref x1
ON x1.item_id = x.item_id
AND x1.oem_code = '$fielddata'
OR x.item_id = RIGHT('$fielddata', 1)
UNION
SELECT CONCAT('S.', x.item_id) partNum
FROM xref x
WHERE x.oem_code = '$fieldata'
OR x.item_id = RIGHT('$fielddata', 1)
) t1 ON t1.partNum = p.partNum
source to share
I think this should do what you want
SELECT *
FROM parts
WHERE partNum IN
( SELECT x.oem_code
FROM xref x
JOIN xref x1
ON x1.item_id = x.item_id
AND x1.oem_code = '$fielddata'
OR x.item_id = RIGHT('$fielddata', 1)
)
OR partNum IN
( SELECT CONCAT('S.', x.item_id)
FROM xref x
WHERE x.oem_code = '$fieldata'
OR x.item_id = RIGHT('$fielddata', 1)
)
just use join so you don't have to do the third subquery
source to share