Saurabh Srb Saurabh Srb - 16 days ago 6
SQL Question

Select Unique rows from two tables

We have 3 tables in MySQL database,

Industries Table

industries

| pkIndustryID | industryName |
|--------------|------------------------|
| 1 | Accounting and Finance |
| 2 | Legal |
| 3 | Health Care |


User table

users

| pkUserID | UserName |
|----------|------------------|
| 1 | Saurabh Sharma |
| 2 | Avinash Panday |
| 3 | Mrinal Chaudahry |


Relationship table for user and industry

provider_industries

| pkProviderindustryID | fkUserID | fkIndustryID |
|----------------------|----------|--------------|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 2 | 2 |
| 5 | 3 | 1 |
| 6 | 3 | 2 |
| 7 | 3 | 3 |


Now my question is i want users from all insutries but any user should not be repeated . How to achieve this with single SQL query ?

Expected Result is

| pkProviderindustryID | fkUserID | fkIndustryID |
|----------------------|----------|--------------|
| 1 | 1 | 1 |
| 4 | 2 | 2 |
| 7 | 3 | 3 |


I don't want the user and industries to be repeated !
Result should show distinct user with distinct industry. InsustryID or userID any of them should not be repeated

Any help will be appreciated !!!
Thanks !!!

Answer

Try this

select p.pkProviderindustryID, u.fkUserID, i.fkIndustryID 
from industries i 
join provider_industries p on i.pkIndustryID =p.fkIndustryID  
join users u on p.fkUserID = u.pkUserID 
group by u.pkUserID