Retrieve all not logged in users from mysql tables (SQL query)

Question

I have 2 tables listed below:

+-------------+-----------+--------------+-------------------------------+
| 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 |
+-------------+-------------+---------------+

What i want is to select all customers which haven't logged in yet (note the login key in v3_customer_activity). So in this case that would be the customer with customer_id: 3

I'm using a mysql database.

I have tried using the following query:

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)

In the hope it would iterate between the rows found in the subquery. This resulted in an error telling me a subquery may not contain multiple rows.

TL;DR

What I want is to retrieve every customer from v3_customer who is not listed in the table v3_customer_activity

Answer 1

Try this:

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 table with v3_customer_activity and filter records which are not matched.

• Baklap4: I didn't know v3_customer.* is valid mysql, the more you know!
• Abhishek: @Baklap4: No Worries. My Pleasure :)

Answer 2

select v3_customer.* from v3_customer 
where v3_customer.customer_id not in (SELECT v3_customer_activity.customer_id FROM v3_customer_activity)