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"
SELECT records.id FROM records, data WHERE data.id = records.lastname AND data.value = "smith"