How to Select Records Matching Multiple Criteria on the Same Column in SQL?

Select records matching multiple conditions on the same column in SQL

SQL queries sometimes need to retrieve records that match multiple conditions on the same column. However, simply using multiple WHERE clauses with the same columns does not always produce the expected results. Consider the following example:

We have a database table containing contacts and flags indicating the status of each contact. The table contains the following data:

ID	contactid	flag	flag_type
118	99	Volunteer	1
119	99	Uploaded	2
120	100	Via Import	3
121	100	Volunteer	1
122	100	Uploaded	2

The goal is to only return contact IDs that match both the "Volunteer" and "Uploaded" flag conditions. Use the following query:

<code class="language-sql">SELECT contactid
WHERE flag = 'Volunteer'
AND flag = 'Uploaded'</code>

No results will be returned since each condition only checks for a specific flag value. To retrieve the required data we need to use an alternative method.

Use GROUP BY and HAVING

One solution is to use GROUP BY and HAVING clauses. The following query will group the results by contactid and count the number of matching flag values:

<code class="language-sql">SELECT contact_id
FROM your_table
WHERE flag IN ('Volunteer', 'Uploaded')
GROUP BY contact_id
HAVING COUNT(*) = 2 -- 必须与WHERE flag IN (...)列表中的数字匹配</code>

Contacts with both "Volunteer" and "Uploaded" flags will be counted as 2. The HAVING clause then filters the results to include only these contacts.

Use connection

Another way is to use connections. The following query will perform a self-join on your_table table:

<code class="language-sql">SELECT T1.contact_id
FROM your_table T1
JOIN your_table T2 ON T1.contact_id = T2.contact_id AND T2.flag = 'Uploaded'
-- // 如果需要，可以添加更多连接
WHERE T1.flag = 'Volunteer'</code>

This query will create a temporary table containing the combination of all contacts that have both the "Volunteer" and "Uploaded" flags. The WHERE clause then filters the results to only include contacts that meet these two conditions.

Method selection

The best way to select records with multiple conditions on the same column depends on the size and structure of your data. For large data sets with long lists of matching criteria, the GROUP BY and HAVING solutions may be more efficient. For small data sets with a shorter list of matching criteria, the JOIN solution may be faster.

The above is the detailed content of How to Select Records Matching Multiple Criteria on the Same Column in SQL?. For more information, please follow other related articles on the PHP Chinese website!