MYSQL 4表查询

Question

我可以在MYSQL查询中使用一些专家建议，我正试图把它们放在一起。

我想做的是：

我正在尝试创建一个允许用户跨多个表执行高级搜索的页面。

这4张表格是：

members, profiles, skills, genre

Members:
*********************************
id | member_id | login | zipcode
*********************************

Profiles:
*********************************************************************
id | member_id | exp | commitment | practice | gigs | availability 
*********************************************************************

Skills:
************************************************************************
id | member_id | lead_vocals | background_vocals | guitar | bass| drums
************************************************************************

 Genre:
********************************************************************************
id | member_id | alternative | classic_rock | modern_rock | blues | heavy_metal
********************************************************************************

Skills and Genre represent check box values checked or not (1 or 0)

搜索表单将是一系列复选框和下拉列表，允许用户指定要搜索的特定项。

我需要帮助的是：

我需要帮助想出最好的方法把这个查询放在一起。我一直在阅读Joins、Union、Sub查询和派生表。我可以进行一些基本查询并获取部分数据，例如：

SELECT members.member_id FROM members LEFT JOIN skills ON members.member_id  = skills.member_id WHERE skills.leadvocals = 1    

然而，我似乎不能把所有的事情都集中在一起。

An example of the search criteria would look something like this:

A user fills out the form and wants to search for all members with  (members table)  zipcode = 11111 OR zipcode = 22222 (profiles table) commitment = ANY, practice = ANY, gigs = 1, availability = ANY  (skills table) lead_vocals = 1 and lead_guitar = 1 (genre table) alternative = 1, modern_rock = 1, heavy_metal = 1

注意，我已经有了计算邮政编码距离的逻辑，并返回了这个范围内的邮政编码列表。

最后，查询只需要返回一个带有member_id的结果列表，并从符合条件的members表中登录。

我并不是想找人来提供这个答案(尽管我不介意答案：)，我想通过自己解决这个问题来学得更好，但我需要一些帮助才能开始。

提前谢谢。

Answer 1

问题中的SQL查询似乎是有效的，您只需要添加其余的表和条件就可以获得所需的数据。

用户填写表单，并希望搜索所有成员(成员表) zipcode = 11111或zipcode = 22222 (概要表)承诺= ANY，practice = ANY，gigs = 1，availability = ANY (技能表) lead_vocals =1和lead_guitar =1(流派表) alternative = 1，modern_rock = 1，heavy_metal =1

您已经将查询的一半放在您的请求中，除了它是用英语编写的，而且碰巧SQL只是英语的一个小子集。

您提到的表出现在FROM子句中：

FROM members m
  INNER JOIN profiles p USING (member_id)
  INNER JOIN skills s USING (member_id)
  INNER JOIN genre g USING (member_id)

条件出现在WHERE子句中：

WHERE p.gigs = 1
  AND s.lead_vocals = 1 AND s.guitar = 1
  AND g.alternative = 1 AND g.modern_rock = 1 AND g.heavy_metal = 1

允许ANY值的字段不会出现在查询中，它们不会过滤结果。

可以使用zipcode操作符搜索多个值以查找IN：

  AND m.zipcode IN ('11111', '22222')

最后，查询只需要返回一个带有member_id的结果列表，并从符合条件的members表中登录。

要返回的字段将转到SELECT子句：

SELECT m.member_id, m.login

也许您希望按特定顺序获取成员列表，例如，按其登录名排序：

ORDER BY m.login

..。或者用他们的一些技巧；把主唱放在列表的前面：

ORDER BY s.lead_vocals DESC

(命令DESCending将1放在列lead_vocals的0前面

现在，如果将所有内容组合在一起，就会得到完整的查询：

SELECT m.member_id, m.login
FROM members m
  INNER JOIN profiles p USING (member_id)
  INNER JOIN skills s USING (member_id)
  INNER JOIN genre g USING (member_id)
WHERE p.gigs = 1
  AND s.lead_vocals = 1 AND s.lead_guitar = 1
  AND g.alternative = 1 AND g.modern_rock = 1 AND g.heavy_metal = 1
  AND m.zipcode IN ('11111', '22222')
ORDER BY s.lead_vocals DESC, m.login

因为您从用户输入中获取信息，所以您事先不知道，例如，允许practice具有ANY值。您需要使用从表单接收到的数据，从各个部分组成查询。

Answer 2

尝试这个查询。

select
m.*
from 
members m
left join Profiles p on p.member_id = m.id
left join Skills s on s.member_id = m.id
left join Genre g on g.member_id = m.id
where (m.zipcode = 11111 OR m.zipcode = 22222) and p.gigs = 1 and s.lead_vocals = 1 and s.lead_guitar = 1 and g.alternative = 1, g.modern_rock = 1, g.heavy_metal = 1