MySQL查询需求：匹配名称后获取同med字段的所有关联记录

Got it, let's tackle this problem. You want your search to not just return records that match the keyword in name or med, but also pull in every other record that shares the same med value as those matching records. Here are two straightforward ways to make this happen:

Method 1: Using a Subquery #

This approach first identifies all unique med values from records that fit your original search criteria, then fetches all records tied to those med values.

SELECT * FROM item
WHERE med IN (
    -- First get all distinct med values from matching records
    SELECT DISTINCT med FROM item
    WHERE name LIKE '%{$search}%' OR med LIKE '%{$search}%'
)

How it works: #

1. The inner subquery runs your original search logic to find records where name or med contains the search term. It then extracts unique med values from those matches.
2. The outer query grabs every record in the table whose med value is in that list of unique matches. For your example search "sec", the subquery returns omeprazole, so the outer query pulls both id 1 and id 3—exactly what you need.

Method 2: Using a Self-Join #

You can also use a self-join to link matching records with other records that have the same med value, then select distinct results to avoid duplicates.

SELECT DISTINCT i.* FROM item i
-- Join the table to itself on matching med values
JOIN item match_item ON i.med = match_item.med
-- Filter for the original search criteria on the matched records
WHERE match_item.name LIKE '%{$search}%' OR match_item.med LIKE '%{$search}%'

Critical Note: Avoid SQL Injection #

A quick important reminder: Using %{$search}% directly in your query leaves you open to SQL injection attacks. Instead, use parameterized queries with prepared statements (like PDO or MySQLi in PHP) to safely pass the search value. For example, in PDO:

$stmt = $pdo->prepare("SELECT * FROM item WHERE med IN (SELECT DISTINCT med FROM item WHERE name LIKE ? OR med LIKE ?)");
$searchTerm = "%{$search}%";
$stmt->execute([$searchTerm, $searchTerm]);
$results = $stmt->fetchAll();

This keeps your query secure while delivering the exact result you want.

内容的提问来源于stack exchange，提问作者Sharfuddin Shawon