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" <[EMAIL PROTECTED]>
To: "ACEAlex" <[EMAIL PROTECTED]>
Cc: "php-db" <[EMAIL PROTECTED]>
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
> [EMAIL PROTECTED]
> +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
