3

I'm trying to conform an existing database and system to data we are now receiving from a manufacturer. Its format can't/won't be changed. It is a table with product numbers (item_id) from a single manufacturer that cross reference parts from multiple manufacturers (oem_code).

Table: xref

item_id   | oem_code
   A      |    123
   A      |    234
   B      |    234
   B      |    345
   C      |    456

Table: parts (only showing relevant partNum column)

partNum  
  S.A  
  S.B  
  S.C  
  123  
  234  
  345  
  456

Both xref columns contain part numbers in the parts table. I need to enter a part number ($fielddata) and then retrieve all data from parts where parts.partNum = $fielddata, as well as all cross referenced numbers from xref that match parts.partNum. Unfortunately, the part numbers from the manufacturer have S. preceding them in our system but not in the table they are sending. Our system won't recognize them without the S.

MySQL query is 

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 (needs also S.A)
234 errors out, #1242 - Subquery returns more than 1 row (needs 234, S.A, S.B)
A returns nothing (needs 123, 234, S.A)
S.A also returns nothing (needs 123, 234, S.A)

I understand the limitations of my sql statement and subquery and why I'm not getting the full result set desired, but I don't know how to expand it to get what I want. I was happy to get as far as I did with it. When I try to expand it, I get undesired results or errors, so I'm pretty much at the end of my ability.

I'm also aware that running subqueries aren't efficient on large databases. So I'm up for a total rewrite of the sql or a modification of what I've got so far. xref has >27k rows and parts has >550k rows with 10 columns (if anyone cares).

Any help would be greatly appreciated.

Improve this question
1
  • Can you edit your question and provide sample results? I'm not sure what such a query is supposed to be returning. Commented Aug 26, 2014 at 22:11

2 Answers 2

Reset to default
2

A 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 may be faster to join against a derived table for a given $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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

but I think you need to add to the WHERE.. x2.oem_code = '$fielddata' OR x1.item_id = '$fielddata' to handle if the OP inputs 'A' as the parameter
even better use RIGHT('$fielddata',1) to handle 'A' OR 'S.A' :) .. so it would be WHERE (x2.oem_code = '$fielddata' OR x2.item_id = RIGHT('$fielddata',1))
I ran this on a test db last night and without the full weight of the live parts table, it was very quick. Running it on the live data directly from the server times out on an empty result set.
@FuzzyTree thanks for the update. I'm consistently getting results around 19 seconds. We're going in the right direction, but that's still too long. Can't tell you how much I appreciate your help, though. It would have taken me weeks to get even close to this on my own.
@GregBuck what indexes do you have on the tables?
1

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)
)

DEMO

just use a join so you aren't doing a third subquery

Improve this answer

3 Comments

the 2nd subquery doesn't require a join i.e. select concat('S.',x.item_id) from xref x where x.oem_code = '$fieldata' or x.item_id = '$fielddata'
This is actually quicker than the other code, but still nowhere near fast enough for production. Am I expecting too much with this? Like I said in the original post, xref has >27k rows to cross reference with >550k in parts.
@GregBuck what indexes do you have on the table? I believe you should be able to optimize this with some indexes to make it go faster. but you may need to do some more research on it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.