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)

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

我需要什么帮助:

我需要帮助找出将该查询组合在一起的最佳方法。我一直在阅读有关联接、联合、子查询和派生表的内容。我可以做一些基本查询并获取部分数据，例如:

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 和登录名且符合条件的结果列表。

我并不是在寻找某人来提供这个答案(尽管我不介意这个答案:))我通过尝试自己找出答案来学得更好，但我需要一些帮助来开始。

提前致谢。

Answer 1

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

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

您已经在请求中放入了一半的查询，只不过它是用英语编写的，而且 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值的字段不会出现在查询中，它们不会过滤结果。

可以使用 IN 运算符搜索多个 zipcode 值:

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

At the end of the day the query just needs to return a list of results with member_id and login from the members table that match the criteria.

要返回的字段转到 SELECT 子句:

SELECT m.member_id, m.login

也许您想按特定顺序获取成员列表，例如按登录名排序:

ORDER BY m.login

...或者通过他们的一些技能；将主唱放在列表前面:

ORDER BY s.lead_vocals DESC

(按 DESCending 顺序将 lead_vocals 列中 1 的内容排在 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 值。您需要使用从表单接收的数据来分段组成查询。