如何从基于Mysql的总销售额中获取每个用户名3表中的每个数据

Question

I'm try to getting data from 3 Tables , which is : "artist" , "sales", "claim"

So I wanna try to get each "artist" income (from total "sales") and if they have Claimed some sales which is this data save in tables "claim"

And here's structure each tables :

*Table Artist:
Artist_id
Artist_name
Artist_profile

*Table Sales :
Sales_id
Artist_name
Sales_amount

*Tables Claim :
Claim_id
Artist_name
Claim_amount

I've try some queries and didn't get what I expected some value are looping in column "Claim_amount" , Let say Table "sales" have a several data , and Table "claim" not yet have a data because there's no claim history.

SELECT artist.member_id,artist.profile_pict, sales.artist_name, 
       SUM(sales.amount) AS total_sales ,claim.claim_amount

FROM sales,artist,claim

WHERE sales.artist_name OR claim.artist_name = artist.username

   GROUP BY sales.artist_name
   ORDER BY total_sales DESC 

I expect the output is like example :

Artis_id | Artist_profile | Artist_name  | Total_amount | Total_claim
1          Artist A         My Artist 1    100            50
2          Artist B         My Artist 2    200            null (because there is no history)
3          Artist C         My Artist 3    300            150

Answer 1

Use left join

SELECT a.member_id,a.profile_pict, a.artist_name, 
       SUM(sales.amount) AS total_sales ,sum(claim_amount) as total_claim
FROM artist a left join sales s on a.Artist_name=s.Artist_name
left join claim c on c.Artist_name= a.Artist_name
GROUP BY a.member_id,a.profile_pict, a.artist_name
ORDER BY total_sales DESC 

NOTE: It's always best to use explicit join rather than comma seperated join