基于postmeta表的多条件SQL查询需求：匹配名称位置并按公司名分组

Got it, let's get this sorted out! The issue with your current SQL is that using OR pulls in any record matching either the name or location criteria, but you need companies that meet both requirements. Since your postmeta table uses an EAV (Entity-Attribute-Value) structure—where each row stores a single attribute for an entity—we need to link the two attributes (company name and location) to the same company entity.

Why Your Original Query Fails #

Your current statement:

SELECT meta_value FROM postmeta WHERE (meta_key = 'company_name' AND meta_value LIKE '%apple%') OR (meta_key = 'company_location' AND meta_value LIKE '%paris%') GROUP BY meta_value ORDER BY meta_value

This returns all rows where the name contains "apple" or the location contains "paris", then groups by the meta_value (which could be either a name or a location). This means you'll end up with names that don't match the location filter, and locations that don't match the name filter—definitely not what you want.

Solution 1: Self-Join the Table #

The most straightforward fix is to join the postmeta table to itself, once for the company name and once for the location. This ensures we're only getting companies where both attributes match your filters.

Note: I'm assuming your postmeta table has an entity ID field (like post_id or company_id) that links different attributes to the same company. This is standard for EAV tables—if you don't have this field, your table design is missing a critical piece to associate attributes to entities.

Here's the corrected query:

SELECT pm_name.meta_value AS company_name
FROM postmeta pm_name
INNER JOIN postmeta pm_location
  ON pm_name.post_id = pm_location.post_id -- Link to the same company entity
WHERE
  pm_name.meta_key = 'company_name'
  AND pm_name.meta_value LIKE '%apple%'
  AND pm_location.meta_key = 'company_location'
  AND pm_location.meta_value LIKE '%paris%'
GROUP BY pm_name.meta_value
ORDER BY pm_name.meta_value;

Solution 2: Use an EXISTS Subquery #

If you prefer subqueries over joins, you can use EXISTS to check that a matching location record exists for the same company entity:

SELECT meta_value AS company_name
FROM postmeta pm_main
WHERE
  pm_main.meta_key = 'company_name'
  AND pm_main.meta_value LIKE '%apple%'
  AND EXISTS (
    SELECT 1
    FROM postmeta pm_location
    WHERE
      pm_location.post_id = pm_main.post_id -- Same company entity
      AND pm_location.meta_key = 'company_location'
      AND pm_location.meta_value LIKE '%paris%'
  )
GROUP BY meta_value
ORDER BY meta_value;

Key Takeaway #

For EAV tables, you can't use simple AND/OR across different rows—you need to explicitly link rows that belong to the same entity. Without that entity ID field, you won't be able to reliably associate company names with their locations, so make sure your table includes that if it doesn't already.

内容的提问来源于stack exchange，提问作者Faisal Khurshid