MySQL根据另一个表中的数据从一个表返回结果 [英] MySQL returning results from one table based on data in another table

问题描述

在深入探讨问题之前，我将首先说明情况.我有两个表，如下所示:

Before delving into the issue, first I will explain the situation. I have two tables such as the following:

USERS TABLE
user_id
username
firstName
lastName

GROUPS TABLE
user_id
group_id

我想检索所有姓LIKE'％foo％'并且属于group_id ='givengid'的用户

I want to retrieve all users who's first name is LIKE '%foo%' and who is a part of a group with group_id = 'givengid'

因此，查询需要这样的内容:

So, the query would like something like this:

SELECT user_id FROM users WHERE firstName LIKE '%foo'"

我可以创建一个用户定义的sql函数，例如ismember(user_id，group_id)，如果用户是组的一部分，则将返回1，如果不是该组的一部分，则将返回0，并将其返回到上述select语句中的WHERE子句.但是，这意味着对于每个与名字匹配的用户，都必须对成千上万条其他记录进行另一个查询，以找到与组条目匹配的潜在条件.

I can make a user defined sql function such as ismember(user_id, group_id) that will return 1 if the user is a part of the group and 0 if they are not and this to the WHERE clause in the aforementioned select statement. However, this means that for every user who's first name matches the criteria, another query has to be run through thousands of other records to find a potential match for a group entry.

users和groups表将分别具有数十万条记录.使用用户定义的函数方法还是使用UNION语句运行查询是否更常规?如果UNION方法是最好的，那么带有union语句的查询将是什么样?

The users and groups table will each have several hundred thousand records. Is it more conventional to use the user defined function approach or run a query using the UNION statement? If the UNION approach is best, what would the query with the union statement look like?

当然，我将运行基准测试，但是我只想对这种情况下可能的解决方案范围以及通常最有效/最有效的方法有一些看法.

Of course, I will run benchmarks but I just want to get some perspective on the possible range of solutions for this situation and what is generally most effective/efficient.

推荐答案

您应使用

You should use a JOIN to get users matching your two criteria.

SELECT 
  user_id 
FROM 
  users 
INNER JOIN
  groups
  ON groups.user_id = users.users_id
    AND groups.group_id = given_id
WHERE 
  firstName LIKE '%foo'

这篇关于MySQL根据另一个表中的数据从一个表返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！