Chaosxmk Chaosxmk - 3 months ago 6
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:

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

postal_codes
+----+-------------+---------+-----------+
| 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
A1A
, 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%')
OR
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

You can use JOIN

SELECT DISTINCT b.* 
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

SELECT * 
FROM   buildings B 
WHERE  EXISTS (SELECT 1 
               FROM   postal_codes PC 
               WHERE  B.building_code IN ( pc.primary, pc.secondary ) 
                      AND PC.postal_code LIKE '%A1A%')