INTERSECT in MySQL

Question

I have two tables, records and data. records has multiple fields (firstname, lastname, etc.). Each of these fields is a foreign key for the data table where the actual value is stored. I need to search on multiple record fields.

Below is an example query using INTERSECT, but 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"

Thanks for any help.

Answer 1

You can use an inner join to filter for rows that have a matching row in another 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 many other 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'
)

Answer 2

Use joins instead:

SELECT records.id
FROM records
JOIN data AS D1 ON records.firstname = D1.id
JOIN data AS D2 ON records.lastname = D2.id
WHERE D1.value = 'john' and D2.value = 'smith'

Here's some test data:

CREATE TABLE records (id INT NOT NULL, firstname INT NOT NULL, lastname INT NOT NULL);
INSERT INTO records (id, firstname, lastname) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 1),
(4, 2, 2);

CREATE TABLE data (id INT NOT NULL, value NVARCHAR(100) NOT NULL);
INSERT INTO data (id, value) VALUES
(1, 'john'),
(2, 'smith');

Expected result:

2

The test data is probably not useful for the poster, but might be useful for voters who want to check solutions to see that they work correctly, or people who want to submit answers so that they can test their own answers.