codehelp codehelp - 2 months ago 6
MySQL Question

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

Hello I have a form where I can select An Artist by his name or select a song and choose a date range and get statistics from it all, What i am trying to achieve is basically the below,


  • get number of played

  • get number of songs



Problems is both queries I am writing look the same, is there another way to tackle this in a better way ?

here is the 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 for songs (this is working correctly and counting the songs correclty)

$q2 = "
SELECT *
FROM songs s
LEFT
JOIN plays p
ON p.play_id = s.plid
LEFT
JOIN artist a
ON a.aid = s.aid
WHERE (a.artist_name = '$artist' OR s.aid = '$song')
AND date BETWEEN '$start_date' AND '$end_date'";


Query for plays (this one is returning same result as the query form the songs where it should return the number of plays by this artist)

$q1 = "
SELECT *
FROM plays p
LEFT
JOIN songs s
ON s.plid = p.play_id
LEFT
JOIN artist a
ON a.aid = s.aid
WHERE (a.artist_name= '$artist' OR s.aid = '$song')
AND date BETWEEN '$start_date' AND '$end_date'";

Answer

The issue you are experiencing are caused by your data and the way you query it. It is clear from the sample data that a single play can have multiple songs.

This means, when you list the matching records from both the plays and the songs table, then the number of records returned will be determined by the songs table because it has the duplicate data.

To get the count of plays back, you need to count the number of distinct play ids returned by the query. Simple count would return 5 because of the duplications in the song table. Also, you do not need left joins as explained by @xQbert, so I just used inner joins, since you want matching plays only. I also removed the filtering on the songs, since you insists that the 2nd query should return the number of plays of a given artist. You are free to add that condition back.

$q1 = "
SELECT count(distinct p.play_id) as count_of_plays 
  FROM plays p
  INNER 
  JOIN songs s
    ON s.plid = p.play_id
  INNER
  JOIN artist a
    ON  a.aid = s.aid
 WHERE a.artist_name= '$artist' 
   AND date BETWEEN '$start_date' AND '$end_date'";
Comments