Crusader Posted December 20, 2006 Share Posted December 20, 2006 [code]// query 1"SELECT * FROM sectors WHERE (x BETWEEN '%s' AND '%s') AND (y BETWEEN '%s' and '%s') ORDER BY x,y limit 9"// query 2"SELECT sector,owner,ship,mothership,name FROM ships WHERE sector='%s' ORDER BY id"// merged queries"SELECT sectors.*,ships.sector,ships.owner,ships.mothership,ships.name FROM sectors,ships WHERE (sectors.x BETWEEN '%s' AND '%s') AND (sectors.y BETWEEN '%s' AND '%s') AND ships.sector=sectors.id ORDER BY sectors.x,sectors.y"[/code]I merged two of my queries to try and save connections... the problem is I'm not sure on how to make it output all the values from the first query.Right now it just seems to get all of the values in query 2 and doesn't output the rest of query 1. How can I get it to output all of the values from query 1 and output the results in query 2 if any exist.---Important Table Structures---sectors:id,x,y,shipsships:sector,id"id" in the "sectors" table corresponds to "sector" in the "ships" table.Right now I'm running both queries seperately, if a result in query 1 says that "ships" is greater than zero then it runs query 2. Any ideas on how I can do the same with one query?Thanks! Quote Link to comment Share on other sites More sharing options...
btherl Posted December 20, 2006 Share Posted December 20, 2006 Can you give your actual output and expected output? I'm having trouble understanding from your description.The query looks logical. It should return a row with ship data from the ships table for every ship in the sectors with x between %s and %s and y between %s and %s. Quote Link to comment Share on other sites More sharing options...
Crusader Posted December 20, 2006 Author Share Posted December 20, 2006 The output for query 1 brings up to 9 results (sector ids, etc).If there's a specific value found in query 1 then it will run query 2 to look for a matching sector id.My merged query only does what query 2 should, it gets results from query 1 but only prints out results that are valid in query 2. Quote Link to comment Share on other sites More sharing options...
btherl Posted December 20, 2006 Share Posted December 20, 2006 Can you paste the actual output from each of the queries?Do you want to keep results from query 1 even when there is no matching ship from query 2? If so, use:[code=php:0]"SELECT sectors.*,ships.sector,ships.owner,ships.mothership,ships.name FROM sectors LEFT JOIN ships ON (ships.sector = sectors.id) WHERE (sectors.x BETWEEN '%s' AND '%s') AND (sectors.y BETWEEN '%s' AND '%s') ORDER BY sectors.x,sectors.y"[/code]That will put nulls for the ships table where there is no matching row. Quote Link to comment Share on other sites More sharing options...
Crusader Posted December 20, 2006 Author Share Posted December 20, 2006 The LEFT JOIN actually did the trick! Thank you so much for such a quick answer! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.