Jump to content

Multiple Queries


ameliabob
 Share

Recommended Posts

I am trying to do a query on a table and also do a join. I am not sure how to go about doing this. My original query looked like this: $qry = "SELECT title,type,lastused, location FROM songs, seasonref WHERE seasonref.seasonID = ' ".$seasonNo." ' AND seasonref.songID = songs.P_id ORDER BY title" What I am trying to do is the first part of the WHERE is a query that selects a series of pointers (songID) that is used in the second part after the AND. The structure of the seasonref table isseasonID intsongID int the songs table hasP_id inttitle varchar(40)type varchar(10)etc. Would I be better having the output of the first query feed into the second? If so how do I do this? ThanksBob

Link to comment
Share on other sites

This is the query SELECT title,type,lastused, location FROM songs, seasonref WHERE seasonref.seasonID = ' ".$seasonNo." ' AND seasonref.songID = songs.P_id ORDER BY title When I referred to the 1st and second it was more to the pieces of the queery. the first piece is between the WHERE and the AND. The second piece is after the AND. $seasonNo is defined as a single changeable value that is set before the query.

Link to comment
Share on other sites

That's not SQL, that's part of your PHP code. MySQL doesn't see something called "$seasonNo" in that query, it sees whatever the value of that variable was in PHP. For the sake of terminology, the parts of the WHERE clause are usually referred as conditions or expressions, the query is the entire statement that the database runs. Anyway, I'm not real sure what your question is. The two conditions don't really interact with each other. What that query does is first create a temporary table that contains every possible combination of records from the songs and seasonref tables, and then it filters out everything that doesn't match the given seasonID, then it filters out everything where the songID and P_id don't match. If you want it to start with a temporary table that only contains the matching records instead of every possible combination then use a specific join with a join condition. SELECT title,type,lastused, location FROM songs INNER JOIN seasonref ON seasonref.songID = songs.P_id WHERE seasonref.seasonID = ' xxx' ORDER BY title

Link to comment
Share on other sites

  • 1 month later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...