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

closed as off-topic by ypercubeᵀᴹ, Philᵀᴹ, Max Vernon, Colin 't Hart, RolandoMySQLDBA Mar 14 '15 at 20:05

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – ypercubeᵀᴹ, Philᵀᴹ, Max Vernon, Colin 't Hart, RolandoMySQLDBA
If this question can be reworded to fit the rules in the help center, please edit the question.


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 ;
  • 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.