CODEWITHSUNDEEP
mysqlsql
Baklap4
Baklap4

Reputation: 4202

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

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

Upvotes: 1

Views: 45

Answers (2)

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

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

Upvotes: 0

Abhishek Ginani
Abhishek Ginani

Reputation: 4751

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.

Upvotes: 3

Related Questions