0

I have three tables.

managersTbl

ID   | NAME |
1    | Ana  |
2    | Elsa |
3    | Olaf |
4    | Belle|

gigsTbl

ID   | GIG       | EARNING | MANAGER | ARTIST |
1    | sing      | 500     | 2       | 1      |
2    | act       | 100     | 2       | 3      |
3    | modelling | 250     | 3       | 4      |
4    | dance     | 10      | 1       | 1      |

artistsTbl

ID   | NAME   |
1    | Haley  |
2    | Aw     |
3    | Fire   |
4    | Finn   |

What I want to accomplish is get all the managers, and get the sum of their gigs earning.

SELECT  id, name
ifnull(b.earning, 0) AS earning
FROM doctorsTbl
LEFT JOIN(
    SELECT id, name
    SUM(earning) AS earning
    FROM gigsTbl
    GROUP BY manager_id
) b on (a.id = b.manager_id)

I have already achieved getting all the managers, and the sum of their gigs earnings with the above query. Now, I want to get all the artists, without getting repeated, from the gigsTbl grouped by their manager.

I've tried using inner join like this

SELECT  id, name
ifnull(b.earning, 0) AS earning
FROM doctorsTbl
LEFT JOIN(
    SELECT id, name
    SUM(earning) AS earning
    FROM gigsTbl
    INNER JOIN artistsTbl d
    on b.artist = d.id
    GROUP BY manager_id
) b on (a.id = b.manager_id)

I want to do this without creating a new query for the artists because I'd like to be able to search the tables with using the artist identification.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Hani
  • 394
  • 4
  • 11
  • 2
    We will need a bit of sample data. Ideally one set of data from each table that represents the problem. – Aaron Dietz Jan 24 '18 at 16:01
  • 1
    "Then get all patient information, uniquely , for those consultations." . . . What does this mean? – Gordon Linoff Jan 24 '18 at 16:03
  • Hi, I've updated my question. Added more information. Sorry, I used a wrong analogy earlier. – Hani Jan 24 '18 at 16:49
  • It's not clear what you want or how it or the queries you have given relate to each other, please edit to be clear and to give a [mcve]. (Which should get rid of doctorsTbl.) Eg it's not clear whether by "I want to get all the artists, without getting repeated" you mean you want to add more columns to the preceding query or you want a different query. Use enough words, phrases & sentences to clearly explain everything about each phase of what you have done & want without shortcuts. PS It seems possible this will end up being solved by [this](https://stackoverflow.com/a/45252750/3404097). – philipxy Jan 25 '18 at 04:04

0 Answers0