MYSQL从基于多行的表中选择 [英] MYSQL Select from tables based on multiple rows

问题描述

我有一个名为user_meta的表.在该表中，我有以下几列:ID，userID，meta_key，meta_value

I have a table called user_meta. In that table I have the following columns: ID, userID, meta_key, meta_value

我还有一个名为users的表，其中唯一重要的列是ID，我想将其与user_meta表行进行比较.

I have another table called users, the only important column there is ID, which I want to compare to the user_meta table rows.

users表看起来像:

ID    |    email   | etc...
1     |    [e-mail]  |
5     |    [e-mail]   |
6     |    ....   |
7     |    ....   |

所以说我有一个看起来像这样的表(user_meta)

So say I have a table (user_meta) that looks like:

ID   |   userID   |   meta_key  |   meta_value
2    |   1        |   companyID |   2
3    |   1        |   user_type |   staff
4    |   5        |   companyID |   2
5    |   5        |   user_type |   staff
6    |   6        |   companyID |   4
7    |   6        |   user_type |   customer

我想为每个userID检索一行，但前提是公司ID和user_type正确.

I want to retrieve a single row for each userID, but only if the company ID and user_type are correct.

我想检索在查询中发送的具有相同companyID的所有用户，因此，假设$ companyID = 2，然后所有具有user_type ='staff'的用户.

I want to retrieve all users that have the same companyID that I would send in the query, so let's say $companyID=2, and then all users that have the user_type='staff'.

因此，user_meta.userID必须等于users.ID，user_meta.companyID必须等于2，user_meta.user_type必须等于"staff".

So user_meta.userID must equal users.ID, and user_meta.companyID must equal 2, and user_meta.user_type must equal 'staff'.

我想要一个符合这些条件的所有用户的列表.

I want a list of all users that match these criteria.

结果将为userID 1& 5个被返回.它们都具有companyID = 2，并且都具有user_type = staff

A result would be userID 1 & 5 are returned. They both have companyID = 2, and both have user_type = staff

推荐答案

对于每个要匹配的属性，您都需要与user_meta加入一次.

You need to join with user_meta once for each attribute you want to match.

SELECT u.*
FROM users AS u
JOIN user_meta AS m1 ON u.id = m1.userID
JOIN user_meta AS m2 ON u.id = m2.userID
WHERE m1.meta_key = 'companyID' AND m1.meta_value = :companyID
AND m2.meta_key = 'user_type' AND m2.meta_value = 'staff'

这篇关于MYSQL从基于多行的表中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！