CODEWITHSUNDEEP
phpmysql
Greg Buck
Greg Buck

Reputation: 31

Mysql query/subquery

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.

Upvotes: 3

Views: 86

Answers (2)

FuzzyTree
FuzzyTree

Reputation: 32392

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

Upvotes: 2

John Ruddell
John Ruddell

Reputation: 25842

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

Upvotes: 1

Related Questions