Selecting From 2 Tables Based On DISTINCT results

My SQL skills are deeply lacking and I’m running into an issue whereas breaking all my queries out into PHP loops are driving up load time so I’m looking to see if I can learn to use MySQL in a more efficient manner.

I’ve got 2 tables:

CREATE TABLE chart_songs (
id int(11) NOT NULL auto_increment,
chart_id int(11) default NULL,
song_id int(5) default NULL,
song_title varchar(255) default NULL,
artist_name varchar(255) default NULL,
label varchar(255) default NULL,
spins int(11) default NULL,
finalized tinyint(4) default NULL,
user int(5) default NULL,
chart_week int(5) default NULL,
new_add tinyint(1) default NULL,
chart_date datetime default NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE active_songs (
id int(11) NOT NULL auto_increment,
song_title varchar(255) default NULL,
artist_name varchar(255) default NULL,
label varchar(255) default NULL,
hide_from_chart varchar(255) default NULL,
created datetime default NULL,
created_week int(11) default NULL,
user_added tinyint(4) default NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

**

First I need to get the # of distinct song_id’s from my chart_songs table:

SELECT DISTINCT(song_id) FROM chart_songs WHERE chart_week = ‘35’;

Once I’ve got the results (currently about 200) I need then to loop through the active_songs table and get the data for each song (song name, artist, etc…)

Once I get these results I put this into a PHP loop and run my process which should be fine for now until I get this first piece figured out.

My approach in php would be something like:


   $sql = myql_query("SELECT DISTINCT(song_id) FROM chart_songs WHERE chart_week = '35'") or die(mysql_error());
  while($row=mysql_fetch_array($sql)) {

     $sql2 = mysql_query("SELECT * FROM active_songs WHERE id = '".mysql_real_escape_string($row['song_id'])."' LIMIT 1") or die(mysql_error());
     $row2=mysql_fetch_array($sql2);

       ### RUN ROUTINE CODE HERE ####


 }

I’ve got a hunch that there is a much easier / efficient way to do this directly in MySQL but need someone to point me in the right direction. I just can’t get my head around JOIN / GROUP / Nested Queries since I haven’t used them before.

Thanks for your help.

Scallio - awesome explanation… you should write a book.

Thanks for your help.

jw

In this case you’d want to join the chart_songs with active_songs table. Since for every id in chart_songs there is a corresponding row in active_songs (I hope) you can use an INNER JOIN.

The logic is that you want to “glue” two tables together to one table, and in order to that you need to tell MySQL which columns of both tables correspond. In your case the values of chart_songs.song_id correspond with the values of active_songs.id

The query is as follows


SELECT
   DISTINCT(chart_songs.song_id)
 , something
 , anything
 , just_not_the_dreaded_star
FROM
  chart_songs
    INNER JOIN # this tells MySQL to join chart_songs with another table
  active_songs # this is the table we want to join with
    ON
  chart_songs.song_id=active_songs.id # Tell MySQL how to "glue" the two tables together
WHERE
  chart_songs.chart_week=35

Voila :slight_smile: