在MySQL中，可以通过使用INNER JOIN或者IN子句来实现对多个字段的联合搜索。例如，可以将数据表与记录表通过INNER JOIN连接，分别匹配firstname和lastname字段，并过滤出值为'john'和'smith'的记录。另一种方法是使用IN子句，通过子查询找出firstname和lastname对应的id，然后在主查询中过滤出这些id的记录。
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.
解决方案
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'
)
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
