检索所有没有登录用户从MySQL表(SQL查询)
问题描述:
我有如下2个表:检索所有没有登录用户从MySQL表(SQL查询)
+-------------+-----------+--------------+-------------------------------+
| v3_customer |
+-------------+-----------+--------------+-------------------------------+
| customer_id | firstname | lastname | email |
+-------------+-----------+--------------+-------------------------------+
| 1 | Piet | Pizza | [email protected] |
| 2 | Klaas | Hein | [email protected] |
| 3 | Henk | Crowdcontrol | [email protected] |
+-------------+-----------+--------------+-------------------------------+
+-------------+-------------+---------------+
| v3_customer_activity |
+-------------+-------------+---------------+
| activity_id | customer_id | key |
+-------------+-------------+---------------+
| 1 | 1 | login |
| 2 | 1 | order_account |
| 3 | 2 | login |
+-------------+-------------+---------------+
我要的是选择已经没有登录的所有客户(注意登录键v3_customer_activity
)。所以在这种情况下,这将是客户customer_id:3
我正在使用mysql数据库。
我曾尝试使用下面的查询尝试:
SELECT DISTINCT v3_customer.customer_id, v3_customer.firstname, v3_customer.lastname, v3_customer.email FROM `v3_customer` JOIN v3_customer_activity ON v3_customer.customer_id = v3_customer_activity.customer_id WHERE v3_customer.customer_id != (SELECT v3_customer_activity.customer_id FROM v3_customer_activity)
在此希望它会在子查询中发现的行之间循环。 这导致了一个错误,告诉我一个子查询可能不包含多行。
TL; DR
我想要的是检索v3_customer
每一个客户谁不是在表v3_customer_activity
答
上市试试这个:
select v3_customer.* from v3_customer
left join v3_customer_activity on v3_customer.customer_id=v3_customer_activity.customer_id
where v3_customer_activity.customer_id is null;
LEFT JOIN v3_customer
表v3_customer_activity
和不匹配的过滤器记录。
答
select v3_customer.* from v3_customer
where v3_customer.customer_id not in (SELECT v3_customer_activity.customer_id FROM v3_customer_activity)
非常感谢:D! – Baklap4
我不知道v3_customer。*是有效的mysql,你知道的越多! – Baklap4
@ Baklap4:没有忧虑。我的荣幸 :) –