php - returning different results form 3 tables when the query is written the same way -

hello have form can select artist name or select song , choose date range , statistics all, trying achieve below,

• get number of played
• get number of songs

problems both queries writing same, there way tackle in better way ?

here db structure

3 tables

• plays : play_id | date
• songs : plid | aid
• artist : aid | artist_name

sample variables

$artist = sia $song = chandelier $start_date = 2016-09-06 $end_date = 2016-09-07 

sample db data

plays table

• a- play_id = 12 | date = 2016-09-06

• b- play_id = 13 | date = 2016-09-07

• c- play_id = 14 | date = 2016-09-07

songs table

• a- plid = 12 | aid = 34

• b- plid = 12 | aid = 34

• c- plid = 13 | aid = 34

• d- plid = 14 | aid = 34

• e- plid = 14 | aid = 34

artist table

• a- aid = 34 | artist_name = sia

expected result

• a- number of plays per artist = 3

• b- number of songs per artist = 5

query songs (this working correctly , counting songs correclty)

$q2 = " select *    songs s   left    join plays p     on p.play_id = s.plid   left    join artist     on a.aid = s.aid  (a.artist_name = '$artist' or s.aid = '$song')     , date between '$start_date' , '$end_date'"; 

query plays (this 1 returning same result query form songs should return number of plays artist)

$q1 = " select *    plays p   left    join songs s     on s.plid = p.play_id   left   join artist     on  a.aid = s.aid  (a.artist_name= '$artist' or s.aid = '$song')     , date between '$start_date' , '$end_date'"; 

the issue experiencing caused data , way query it. clear sample data single play can have multiple songs.

this means, when list matching records both plays , songs table, number of records returned determined songs table because has duplicate data.

to count of plays back, need count number of distinct play ids returned query. simple count return 5 because of duplications in song table. also, not need left joins explained @xqbert, used inner joins, since want matching plays only. removed filtering on songs, since insists 2nd query should return number of plays of given artist. free add condition back.

$q1 = " select count(distinct p.play_id) count_of_plays    plays p   inner    join songs s     on s.plid = p.play_id   inner   join artist     on  a.aid = s.aid  a.artist_name= '$artist'     , date between '$start_date' , '$end_date'";