mysql - 我如何从表中的两个查询中获取公共(public)值并将其与 MySql 中的另一个表连接？

Question

我有两张 table 。一个是订单，另一个是用户。我想在 orders 表上运行两个不同的查询，并希望获得共同的值并将其与 users 表连接。

    orders    ======    +----+---------+------------+------------+--------------+    | id | user_id | receive_id | arrival_id | departure_id |    +----+---------+------------+------------+--------------+    | 1  |   1     |     1      |    null    |      5       |    +----+---------+------------+------------+--------------+    | 2  |   2     |     1      |      3     |      6       |    +----+---------+------------+------------+--------------+    | 3  |   3     |     1      |    null    |      7       |    +----+---------+------------+------------+--------------+    | 4  |   1     |     3      |      5     |    null      |    +----+---------+------------+------------+--------------+    | 5  |   3     |     3      |      6     |    null      |    +----+---------+------------+------------+--------------+    | 6  |   4     |     3      |      7     |    null      |    +----+---------+------------+------------+--------------+    | 7  |   1     |     4      |      7     |      9       |    +----+---------+------------+------------+--------------+

    users    =====    +----+---------+    | id |   name  |    +----+---------+    | 1  |  Shaon  |    +----+---------+    | 2  |  Rabu   |    +----+---------+    | 3  |  Asha   |     +----+---------+

1st Query..

SELECT DISTINCT user_id from orders where receive_id = 1 and departure_id != 'null'

Its result will be..1 2 3

2nd Query..

SELECT DISTINCT user_id from orders where receive_id = 3 and arrival_id != 'null'

Its result will be..1 3 4

I want get common values form those queries

That is ..1 3When I Join it to users table then result will be_id__name__arrival_id__departure_id 1  Shaon    null           5 3  Asha     null           7But now I want to see.._id__name__arrival_id__departure_id 1  Shaon     5             5 3  Asha      6             7on arrival_id row the value from where receive_id = 3 there's arrival_id as distinct user_idI think code will be like bellow. But It's not working.

SELECT DISTINCT user_id from orders where receive_id = 1 and departure_id != 'null'
COMMON
SELECT DISTINCT user_id from orders where receive_id = 3 and arrival_id != 'null'
JOIN users on (users.id = orders.user_id)

Answer 1

通过将一个查询作为另一个查询的子查询，您将获得所需的内容。
像这样，

SELECT DISTINCT user_id from orders
JOIN users on (users.id = orders.user_id)
where receive_id = 1 and departure_id != 'null'
and user_id in (SELECT DISTINCT user_id from orders where receive_id = 3 and arrival_id != 'null')

希望这对您有所帮助。