CODEWITHSUNDEEP
mysqlsqlmariadb
Morteza Rahmani
Morteza Rahmani

Reputation: 65

MySQL check if same value exist in another table for three times

I have two MySQL tables:

API_Keys
+----+---------------+--------------+
| id | key_val       | Expiration |
+----+---------------+--------------+
| 1  | 111111111111  | 2018-11-13 |
+----+---------------+--------------+
| 2  | 222222222222  | 2018-11-13 |
+----+---------------+--------------+
| 3  | 333333333333  | 2018-12-13 |
+----+---------------+--------------+
| 3  | 444444444444  | 2018-11-13 |

Used_Keys
+----+------+--------------+
| id | user | key_val      |
+----+------+--------------+
| 1  | john | 111111111111 |
+----+------+--------------+
| 2  | sami | 111111111111 |
+----+------+--------------+
| 3  | mary | 111111111111 |
+----+------+--------------+
| 4  | nina | 333333333333 |
+----+------+--------------+
| 5  | leon | 333333333333 |

Each key_val can be assigned to the maximum of 3 users. So I need to list all API_Keys table data, except the key_vals that are assigned 3 times already in Used_Keys table.

So the desired result of free API_Keys would be:

API_Keys
+----+---------------+--------------+
| id | key_val       | Expiration |
+----+---------------+--------------+
| 1  | 222222222222  | 2018-11-13 |
+----+---------------+--------------+
| 2  | 333333333333  | 2018-12-13 |
+----+---------------+--------------+
| 3  | 444444444444  | 2018-11-13 |

I would appropriate if you help me to write the MySQL select query for this purpose.

Upvotes: 0

Views: 187

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35583

select * 
from api_keys as a
left join (
    select key_val 
    from Used_Keys
    group by key_val 
    having count(*) >= 3
    ) as u on a.key_val = u.key_val 
where u.key_val IS NULL

Upvotes: 1

Related Questions