[SOLVED] SQL inside an SQL

[MadnessRed]

is it possible to excute a command like

 

SELECT * FROM `table1` WHERE `id` = (SELECT `id` FROM `table2` WHERE `banned` = '0') ;

 

rather than having to execute the first command then do an if statement on the second,

[Maq]

Yes, look at subqueries.

[MadnessRed]

ok, so how to I go up a level if that makes sence?

 

"SELECT * FROM `{{table}}statpoints` WHERE `stat_type` = '1' AND `stat_code` = '1' AND (SELECT `bana` FROM `{{table}}users` WHERE `id` = `^id`) <> '1' ORDER BY `". $Order ."` DESC LIMIT ". $start .",100;"

 

^id <= Should be the `id` from the main query, not the sub query.

[Maq]

Please state exactly what you're trying to do or give a specific example.

[MadnessRed]

I have a table of peoples statistics, and I want to show a table with the stats of everyone who is not banned.

 

Actually I think if found a better way of doing it, as I am already calling the user table to get the username, I may as well get the banned data from that and just end the while loop there if they are banned.

[Maq]

Why can't you JOIN the tables together?  Something like this: *not tested*

 

SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.id=t2.id 
WHERE t1.stat_type = 1 AND t1.stat_code = 1 AND t2.banned = 0 
ORDER BY " . $Order . " DESC LIMIT " . $start . " , 100

 

[MadnessRed]

will joining tables be permanent?

[Maq]

No.  How do you think people get data from 2 different tables in 1 query?  All join does is simply temporarily join these tables in one structure.  Please read this.

[MadnessRed]

 

     

 

 

    *       

 

    *       

               

               

 

                 

      Network Timeout

 

               

 

               

               

 

                 

 

      The server at www.devshed.com is taking too long to respond.

 

               

 

             

 

    *       

               

               

                 

                 

                 

               

             

 

           

 

 

   

 

   

   

 

   

   

 

     

What should you do now?

 

 

     

     

 

 

       

       

 

       

       

 

The requested site did not respond to a connection request and the browser has stopped waiting for a reply.

 

    * Could the server be experiencing high demand or a temporary outage?  Try again later.

    * Are you unable to browse other sites? Check the computer's network connection.

    * Is your computer or network protected by a firewall or proxy?  Incorrect settings can interfere with Web browsing.

    * Still having trouble? Consult your network administrator or Internet provider for assistance.

[Maq]

Heh, I was just there...  That's weird, maintenance maybe.

 

Why don't you Google for "msyql what is join"?

 

Have you tried the query I've provided (with minor modification)?

 

 

[MadnessRed]

so what I want is

 

"SELECT * 
FROM {{table}}statpoints LEFT JOIN {{table}}users ON {{table}}statpoints.id = {{table}}users.id 
WHERE `{{table}}statpoints.stat_type` = '1' AND `{{table}}statpoints.stat_code` = '1' AND `{{table}}users.bana` <> '1' ORDER BY `{{table}}statpoints.". $Order ."` DESC LIMIT ". $start .",100;"

 

however when I try that I get

 

Unknown column 'evo1_statpoints.stat_type' in 'where clause'<br />SELECT *

FROM evo1_statpoints LEFT JOIN evo1_users ON evo1_statpoints.id = evo1_users.id

WHERE `evo1_statpoints.stat_type` = '1' AND `evo1_statpoints.stat_code` = '1' AND `evo1_users.bana` <> '1' ORDER BY `evo1_statpoints.total_points` DESC LIMIT 0,100;<br />

 

edit: found the bug in that it was the "evo1_statpoints."

[MadnessRed]

sorry, I can't edit for some reason, anyway it works perfectly now, many thanks.