CODEWITHSUNDEEP
mysql
user3872111
user3872111

Reputation:

mysql inner join interrogate 3 tables

I have a table in a MySQL DB, called ‘users’. The fields for users are : id, email, username, first_name, last_name. Another table in the same MySQL DB, called ‘premium_users’ , contains 2 fields : user_id, premium_service_id. A third table called ‘premium_services’ contains 2 fields : premium_service_id , premium_service_name.

I want to create an SQL query , to interrogate my db, so i can have a full list of what premium services has every premium user. How can i interrogate properly with inner join? I’ve try this :

select * from users inner join premium_users on users.id = premium_users.user_id inner join premium_services on premium_users.premium_service_id = premium_services.premium_service_id;

Upvotes: 1

Views: 107

Answers (1)

sgeddes
sgeddes

Reputation: 62851

Since you say which service has every user, you'll need to use aggregation to determine this. Here's one way:

select user_id
from premium_users
group by user_id 
having count(*) = (select count(*) from premium_services)

Depending on your data, you may need count(distinct premium_service_id) instead, but you should have constraints that don't allow duplicates in those table.

Rereading your question, I might have got this backwards. Looks like you want a list of premium services instead of users. Same concept applies:

select ps.premium_service_id
from premium_services ps
  join premium_users pu on ps.premium_service_id = pu.premium_service_id
group by ps.premium_service_id
having count(distinct pu.user_id) = (select count(distinct user_Id) from premium_users)

Upvotes: 0

Related Questions