Chaosxmk Chaosxmk - 1 year ago 59
MySQL Question

How to select rows where one field is in multiple fields

I'm attempting to query my DB to return all buildings that are a match to an entered postal code. My client has provided me with two tables related to the subject:

| id | name | building_code |
| 1 | Building A | 001 |
| 2 | Building B | 002 |
| 3 | Building C | 003 |

| id | postal_code | primary | pecondary |
| 1 | A1A 1A1 | 001 | 002 |
| 2 | B2B 2B2 | 002 | 003 |
| 3 | C3C 3C3 | 003 | 001 |

Unfortunately for me, the table containing postal codes contains the Building Codes in duplicate (A postal code has a primary building, and a secondary building)

So, when performing a search for buildings with the postal code
, I need to receive Building A and Building B. This isn't that much of an issue in and of itself, however the only method I've found to do it is incredibly bulky and doesn't leave many options for future proofing.

SELECT * FROM buildings
WHERE (building_code IN (SELECT primary FROM postal_codes
WHERE postal_code LIKE '%A1A%')
building_code IN (SELECT secondary FROM postal_codes
WHERE postal_code LIKE '%A1A%'))

I've tried just doing
building IN (SELECT primary, secondary FROM postal_codes)
but that keeps causing errors.

Answer Source

You can use JOIN

FROM   buildings b 
       JOIN postal_codes pc 
         ON b.building_code IN ( pc.primary, pc.secondary ) 
WHERE  postal_code LIKE '%A1A%' 

Note : Used Distinct to avoid duplicate records from Building table since your original does not duplicate result. If your postal_codes table does not have duplicate entries in pc.primary or pc.secondary column you can remove the Distinct

Or you can use EXISTS

FROM   buildings B 
               FROM   postal_codes PC 
               WHERE  B.building_code IN ( pc.primary, pc.secondary ) 
                      AND PC.postal_code LIKE '%A1A%') 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download