I am trying to figure out how to get the proper results from a MySQL database.

Table Structure: items

PN1            PN2
------------   ------------
702866-001     702866-001
738975-001     702866-001
B9C84AV        702866-001
LMT-256M6M     702866-001

The 2nd part # is a cross reference to the master item (PN1), the master item will have PN1 and PN2 duplicated.

The Select statement I needs is that I can search by any part # from either field (PN1 or PN2) and get a complete list of Distinct PN's

For example: If I search for B9C84AV it would return all Part #'s in PN1 list since they are crossed in PN2.

Basically, what I need is a way to get PN2 for the record where PN1 is equal to the search parameter and then return a Distinct list of all records where PN1 or PN2 = PN2 (returned from the search parameter)

Any help on this would be greatly appreciated.

Regards, Bryan


This is rather simple:

SELECT i.pn1 AS pn,
       CASE WHEN i.pn1 = i.pn2 THEN 'master' ELSE 'item' END AS type
FROM items AS i
  JOIN items AS s
    ON s.pn2 = i.pn2
WHERE s.pn1 = @searhed_PN ;
| improve this answer | |
  • It worked perfectly. Thank you so much, I can see the logic in the SQL now, not sure why this has eluded me for so many hours. Again, thanks so much. – Bryan Utley Mar 16 '15 at 16:30

Not the answer you're looking for? Browse other questions tagged or ask your own question.