CODEWITHSUNDEEP
mysql
Michael Ploeckinger
Michael Ploeckinger

Reputation: 1634

Get only rows where all required types are met

We have the following structure in our datadase:

ER diagram

Now we want to get only areas where the user has all the required licenses.

For example:

**required_license_types:**
ticket_type_id: 1 | licenses_type_id: 1
ticket_type_id: 1 | licenses_type_id: 2

**licenses**
user_id: 1 | license_type_id: 1
user_id: 1 | license_type_id: 3
user_id: 1 | license_type_id: 4

We were thinking about a LEFT JOIN, but then we will get also rows with NULL, which MIN cant process.

What would also work, would be todo this in a scheduled job and save it to another table, then we would lack of actuality, but performance would be better.

Any ideas how to solve this?

thanks in advance!

Upvotes: 0

Views: 45

Answers (2)

James Sarrett
James Sarrett

Reputation: 195

As far as the database is concerned, you have 2 problems (queries): First you need to find all the areas the user has a license in Then you need to find all the licenses the area requires

Depending on the existence of duplicates, you could do it by counting the number of results for each query, but I would run them separately and then make the small number of comparisons in the program your dispatched the SQL query from.

To not return rows that have NULL fields you want an INNER JOIN

i.e.

SELECT * from licenses AS l WHERE u.id = '<the user>'
INNER JOIN required_license_types AS rlt ON rlt.license_type_id = l.license_type_id
INNER JOIN ticket_types AS tt ON a.id = tt.area_id
INNER JOIN users AS u ON l.user_id = u.id

and

SELECT * from required_license_types AS rlt WHERE a.id = '<the area>'
INNER JOIN ticket_types AS tt ON tt.id = rtl.ticket_type_id
INNER JOIN areas AS a ON tt.area_id = a.id

Upvotes: 1

Rushikumar
Rushikumar

Reputation: 1812

Per MySQL documentation - Left Join and Right Join Optimization--

If there is a row in A that matches the WHERE clause, but there is no row in B that matches the ON condition, an extra B row is generated with all columns set to NULL.

So yes, you may get NULL rows, given the ON condition is not met. As such, I would recommend using INNER JOIN

Upvotes: 0

Related Questions