INTERSECT in MySQL

Question

I have two tables, records and data records have multiple fields (first name, last name, etc.). All of these fields is a foreign key for the data table where the actual value is stored. I want to search for multiple record fields.

Below is an example query using INTERSECT, however, I need one that works in MySQL.

SELECT records.id FROM records, data WHERE data.id = records.firstname AND data.value = "john"
INTERSECT
SELECT records.id FROM records, data WHERE data.id = records.lastname AND data.value = "smith"

Answer 1

You can use an inner join to filter for rows which have a matching row in the different table:

SELECT DISTINCT records.id 
FROM records
INNER JOIN data d1 on d1.id = records.firstname AND data.value = "john"
INNER JOIN data d2 on d2.id = records.lastname AND data.value = "smith"

One of the various different alternatives is an in clause:

SELECT DISTINCT records.id 
FROM records
WHERE records.firstname IN (
    select id from data where value = 'john'
) AND records.lastname IN (
    select id from data where value = 'smith'
)

Want to be a SQL expert? Enroll in the SQL Training program offered by Intellipaat to learn more and get certified.