INTERSECT in MySQL

by

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"
)

More Related Contents:

  1. Which DB technology is sued to solve the following search issue? [closed]
  2. MySQL pivot row into dynamic number of columns
  3. Delete all Duplicate Rows except for One in MySQL? [duplicate]
  4. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  5. MySQL – Operand should contain 1 column(s)
  6. How to get the difference in years from two different dates?
  7. Restore table structure from frm and ibd files
  8. mysql pivot/crosstab query
  9. MySQL: Split comma separated list into multiple rows
  10. Most efficient way to get table row count
  11. UNION query with codeigniter’s active record pattern
  12. Mysql Convert Column to row (Pivot table )
  13. MySQL order by “best match”
  14. How to select rows that have current day’s timestamp?
  15. Using Count to find the number of occurrences
  16. MySQL concatenation operator
  17. Is there any reason to worry about the column order in a table?
  18. Checking a table for time overlap?
  19. Does mysql have the equivalent of Oracle’s “analytic functions”?
  20. MySql – Way to update portion of a string?
  21. MYSQL select DISTINCT values in two columns
  22. Using reserved words in column names
  23. MySQL INNER JOIN select only one row from second table
  24. Storing IPv6 Addresses in MySQL
  25. Insert/ Update random date in MySQL
  26. Replace null with 0 in MySQL
  27. Get total hours worked in a day mysql
  28. SQL query with avg and group by
  29. Optimistic locking in MySQL
  30. Insert and set value with max()+1 problems

Leave a Comment