How to select multiple tables? with 6 tables

Question

I have a 6 tables

table countries country_id | short_name

table user (if status = 2 active if status = 1 inactive) id | country_id | status

table user_subscribed_disciplines id | user_id |discipline_id

table disciplin id | name

table user_subscribed_study_levels id | user_id | study_level_id

table study_levels id | name

How to select all countries with his parameters? How to write right sql query?

   country | total users count | active users count | inactive users count | discipline subscribers count | top discipline | study level subscribers count | top study level
   USA     |       506         |          500       |     6                |    50                        |          PHD   |          90                   |  Social History
   CAN     |       406         |          406       |     0                |    50                        |          POS   |          0                   |  Social History

A sample of the table data and the SQL you have coded so far would help. — MiguelH, Apr 05 '18 at 13:20
This is a fairly straight-forward join... The number of tables doesn't make it any more complex than a simple two-table join. — Peter Abolins, Apr 05 '18 at 13:20
Possible duplicate of [How can I join multiple SQL tables using the IDs?](https://stackoverflow.com/questions/9853586/how-can-i-join-multiple-sql-tables-using-the-ids) — ppijnenburg, Apr 05 '18 at 13:22
I have already replied to the same question you asked today. Take a look. — ecp, Apr 05 '18 at 13:23
As @MiguelH stated, try to give an example of what you have tried. You can read [here](https://stackoverflow.com/help/mcve) how to do that. — Ewoud, Apr 05 '18 at 13:24
SELECT countries.short_name, COUNT(user.id) FROM user RIGHT JOIN countries ON user.country_id = countries.country_id GROUP BY short_name after that I can do nothing — Karen, Apr 05 '18 at 13:25
Duplicate(?) of https://stackoverflow.com/questions/49668038/how-to-select-multiple-tables/49672306#49672306, In fact... it is a duplicate created by the same user... — Peter Abolins, Apr 05 '18 at 13:30
Have you even tried anything???? Where's your query???? Have you tried to Google it??? There are tons of example online. — Eric, Apr 05 '18 at 15:50

score 1 · Answer 1 · answered Apr 05 '18 at 13:23

You are looking for a query like below I think:

select c.country_id
     , count(u.id) as TotalUsers
     , sum(case when u.status = 'active' then 1 else 0 end) as ActiveUsers
     , sum(case when u.status = 'Inactive' then 1 else 0 end) as InactiveUsers
     , (select count(*) from user_subscribed_disciplines) as DisciplineSubscribers
     , (select name from (select top 1 name,count(*) from disciplines group by name order by count(*) desc) t) as TopDiscipline
     , (select count*) from  user_subscribed_study_levels) as StudyLevelCount
     , (select name from (select top 1 name,count(*) from study_levels group by name order by count(*) desc) t) as TopStudyLevel
from countries c
inner join user u on u.country_id=c.country_id

How to select multiple tables? with 6 tables

1 Answers1