Re: selecting question

As i discribed (or did i??) building_info is for the different types of buildings. Buildings are for those who are currently built, and building_queue are for those who are on queue. I also have a key called planet_id that says on which planet we are looking at. /Alexander ----- Original Message ----- From: "William Fong" <sql@mookandblanchard.com> To: <php-db@lists.php.net> Sent: Thursday, February 21, 2002 12:24 AM Subject: Re: [PHP-DB] selecting question > Ok... so tbl.building_info contains all the different types of buildings. > Didn't get that... :) > > So what are the other types for then? They seem to be identical. > > -- > William Fong - wfong@mookandblanchard.com > Phone: 626.968.6424 x210 | Fax: 626.968.6877 > Wireless #: 805.490.7732 | Wireless E-mail: 8054907732@mobile.att.net > > > > > ----- Original Message ----- > From: "ACEAlex" <alex@saers.com> > To: "William Fong" <sql@mookandblanchard.com>; <php-db@lists.php.net> > Sent: Wednesday, February 20, 2002 1:56 PM > Subject: Re: [PHP-DB] selecting question > > > : Because the buildings that are avaible are stored in building info. There > : are the info that says what the benifit is of that building to :) They are > : not identical. A building "type" can exist on several places :) > : > : /Alexander > : > : ----- Original Message ----- > : From: "William Fong" <sql@mookandblanchard.com> > : To: <php-db@lists.php.net> > : Sent: Wednesday, February 20, 2002 10:46 PM > : Subject: Re: [PHP-DB] selecting question > : > : > : > Hmmm... Why do you want two or three tables? Why not just one? > : > tbl.buildings and tbl.building_queue looks identical. Looks like you > can > : > merge the two and stick it in tbl.building_info. Just add in > : > tbl.building_info a 'status' column. In that, I'd store say > '1' for > : > building in progress, and '2' for finished buildings. > : > > : > ??? > : > > : > -w > : > > : > -- > : > William Fong - wfong@mookandblanchard.com > : > Phone: 626.968.6424 x210 | Fax: 626.968.6877 > : > Wireless #: 805.490.7732 | Wireless E-mail: > 8054907732@mobile.att.net > : > > : > > : > > : > > : > ----- Original Message ----- > : > From: "ACEAlex" <alex@saers.com> > : > To: "Rick Emery" <remery@excel.com>; "php-db" > <php-db@lists.php.net> > : > Sent: Wednesday, February 20, 2002 12:53 PM > : > Subject: Re: [PHP-DB] selecting question > : > > : > > : > : I could make a rutine in php to make it work but i want to make the > : > : datagrabbing thing in mysql :( > : > : > : > : ----- Original Message ----- > : > : From: "Rick Emery" <remery@excel.com> > : > : To: "'ACEAlex'" <alex@saers.com> > : > : Sent: Wednesday, February 20, 2002 6:25 PM > : > : Subject: RE: [PHP-DB] selecting question > : > : > : > : > : > : > > : > : > I'm still trying to work this. There MUST be an easy answer... > : > : > rick > : > : > > : > : > -----Original Message----- > : > : > From: ACEAlex [mailto:alex@saers.com] > : > : > Sent: Wednesday, February 20, 2002 9:30 AM > : > : > To: Markus Lervik > : > : > Cc: php-db > : > : > Subject: Re: [PHP-DB] selecting question > : > : > > : > : > > : > : > Ok. i think i have to make things clearer :).. Here, is my real > mysql > : > : thing. > : > : > I and a friend are trying to make a silly space game and i have > : problems > : > : > with this query. > : > : > Ok, if i set up a database with this > : > : > > : > : > CREATE TABLE building_info ( > : > : > id int(11) NOT NULL auto_increment, > : > : > name varchar(32) default NULL, > : > : > description text, > : > : > PRIMARY KEY (id) > : > : > ) TYPE=MyISAM; > : > : > INSERT INTO building_info VALUES (1,'Factory','N/A'); > : > : > INSERT INTO building_info VALUES (2,'Smaltvark','N/A'); > : > : > INSERT INTO building_info VALUES (3,'Superfarm','N/A'); > : > : > > : > : > CREATE TABLE buildings ( > : > : > id int(11) NOT NULL auto_increment, > : > : > planet_id int(11) default NULL, > : > : > building_id int(11) default NULL, > : > : > PRIMARY KEY (id) > : > : > ) TYPE=MyISAM; > : > : > > : > : > INSERT INTO buildings VALUES (1,1,1); > : > : > INSERT INTO buildings VALUES (2,2,1); > : > : > INSERT INTO buildings VALUES (3,1,2); > : > : > INSERT INTO buildings VALUES (4,6,1); > : > : > INSERT INTO buildings VALUES (5,6,2); > : > : > > : > : > CREATE TABLE building_queue ( > : > : > id int(11) NOT NULL auto_increment, > : > : > building_id int(11) default NULL, > : > : > planet_id int(11) default NULL, > : > : > PRIMARY KEY (id) > : > : > ) TYPE=MyISAM; > : > : > > : > : > INSERT INTO building_queue VALUES (35,2,2); > : > : > INSERT INTO building_queue VALUES (22,2,7); > : > : > INSERT INTO building_queue VALUES (23,1,3); > : > : > INSERT INTO building_queue VALUES (25,1,5); > : > : > INSERT INTO building_queue VALUES (24,1,5); > : > : > INSERT INTO building_queue VALUES (26,2,5); > : > : > INSERT INTO building_queue VALUES (21,1,7); > : > : > INSERT INTO building_queue VALUES (28,1,4); > : > : > INSERT INTO building_queue VALUES (31,3,3); > : > : > > : > : > where building_info is the description of the building. I have > removed > : > : some > : > : > info from it by the way :).. And building_queue is the queue of > : building > : > : > that is to be built that a planet has. And buildings is the > buildings > : > : > actually pressent. > : > : > > : > : > I have another option that says planet_id that specifys the current > : > : planet. > : > : > > : > : > Ok, now i want to make a query that if i ask for planet_id=1 i will > : get > : > : the > : > : > building_info names of those buildings that are not in the queue and > : not > : > : > actually built.I cant get this to work :( > : > : > > : > : > For example,. if you load in this data and asks for planet_id=1 you > : > should > : > : > get > : > : > Superfarm > : > : > planet_id=2 should get > : > : > Superfarm > : > : > planet_id=3 should get > : > : > Smaltvark > : > : > > : > : > And so on :) > : > : > > : > : > Thanx again for responding > : > : > > : > : > /Alexander > : > : > > : > : > ----- Original Message ----- > : > : > From: "Markus Lervik" <markus.lervik@vaasa.fi> > : > : > To: "ACEAlex" <alex@saers.com> > : > : > Cc: "php-db" <php-db@lists.php.net> > : > : > Sent: Wednesday, February 20, 2002 2:31 PM > : > : > Subject: Re: [PHP-DB] selecting question > : > : > > : > : > > : > : > > On Wed, 2002-02-20 at 15:03, ACEAlex wrote: > : > : > > > : > : > > > Hi i have trouble with this mysql query. > : > : > > > > : > : > > > OK, i have 3 different tables. > : > : > > > > : > : > > > Tabel 1: building_info > : > : > > > id > : > : > > > name > : > : > > > price > : > : > > > and other > : > : > > > table 2:queue > : > : > > > id > : > : > > > building_id > : > : > > > table 3:buildings_built > : > : > > > building_id > : > : > > > > : > : > > > Ok, now i want to make a query that gets the data from > : building_info > : > : > where > : > : > > > it is not pressent in the queue and the buildings_built table. > : > : > > > > : > : > > > I have managed to get this to work with only 2 tables. So that i > : can > : > : get > : > : > the > : > : > > > things that are not present in the building_queue or in the > : > : > buildings_built > : > : > > > > : > : > > > Anyony have a nice solution for this? > : > : > > > : > : > > If I understood right, something like > : > : > > > : > : > > SELECT A.id,A.name,A.price > : > : > > FROM building_info AS A, queue AS B, buildings_built AS C > : > : > > WHERE (A.id != B.id AND B.building_id = C.building_id) > : > : > > > : > : > > should do it. I tested it on a simple db > : > : > > > : > : > > building_info("1","foo","200") > : > : > > building_info("2","bar","200") > : > : > > > : > : > > queue("1","1") > : > : > > > : > : > > buildings_built("1") > : > : > > > : > : > > This is what I got: > : > : > > > : > : > > mysql> SELECT A.id,A.name,A.price FROM building_info AS A, queue > AS > : B, > : > : > > buildings_built AS C WHERE (A.id != B.id AND B.building_id = > : > : > > C.building_id)\G > : > : > > *************************** 1. row *************************** > : > : > > id: 2 > : > : > > name: bar > : > : > > price: 200 > : > : > > 1 row in set (0.00 sec) > : > : > > > : > : > > mysql> > : > : > > > : > : > > > : > : > > > : > : > > Cheers, > : > : > > Markus > : > : > > > : > : > > > : > : > > -- > : > : > > Markus Lervik > : > : > > Linux-administrator with a kungfoo grip > : > : > > Vaasa City Library - Regional Library > : > : > > markus.lervik@vaasa.fi > : > : > > +358-6-325 3589 / +358-40-832 6709 > : > : > > > : > : > > > : > : > > -- > : > : > > PHP Database Mailing List (http://www.php.net/) > : > : > > To unsubscribe, visit: > http://www.php.net/unsub.php > : > : > > > : > : > > : > : > > : > : > -- > : > : > PHP Database Mailing List (http://www.php.net/) > : > : > To unsubscribe, visit: > http://www.php.net/unsub.php > : > : > : > : > : > : -- > : > : PHP Database Mailing List (http://www.php.net/) > : > : To unsubscribe, visit: http://www.php.net/unsub.php > : > : > : > : > : > > : > > : > > : > -- > : > PHP Database Mailing List (http://www.php.net/) > : > To unsubscribe, visit: http://www.php.net/unsub.php > : > > : > : > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php >