Join Problem

Join Problem

am 16.08.2010 15:58:36 von Victor Subervi

--000e0cd591e4996659048df13a7a
Content-Type: text/plain; charset=ISO-8859-1

Hi;
I have this code:

select f.id from Flights f join Planes p where f.plane_id=p.id and
p.in_service=1

mysql> describe Flights;
+-------------+-------------------+------+-----+---------+-- --------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+---------+-- --------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| plane_id | int(11) | NO | MUL | NULL | |
| pilot_id | int(11) | NO | MUL | NULL | |
| flight_date | date | NO | | NULL | |
| departure | time | NO | | NULL | |
| arrival | time | NO | | NULL | |
| origination | enum('STT','STX') | YES | | NULL | |
| destination | enum('STT','STX') | YES | | NULL | |
| price | float(6,2) | NO | | NULL | |
+-------------+-------------------+------+-----+---------+-- --------------+

mysql> describe Planes;
+--------------+-------------+------+-----+---------+------- ---------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+------- ---------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| in_service | tinyint(1) | NO | | 1 | |
| capacity | tinyint(2) | NO | | NULL | |
| total_weight | int(6) | NO | | NULL | |
+--------------+-------------+------+-----+---------+------- ---------+

My goal is to exclude results in which in_service !=1; however, the filter
isn't working. Please advise.
TIA,
Victor

--000e0cd591e4996659048df13a7a--

RE: Join Problem

am 16.08.2010 20:36:36 von Gavin Towey

What do you mean by "not working?" What results do you get?

-----Original Message-----
From: Victor Subervi [mailto:victorsubervi@gmail.com]
Sent: Monday, August 16, 2010 6:59 AM
To: mysql@lists.mysql.com
Subject: Join Problem

Hi;
I have this code:

select f.id from Flights f join Planes p where f.plane_id=3Dp.id and
p.in_service=3D1

mysql> describe Flights;
+-------------+-------------------+------+-----+---------+-- --------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+---------+-- --------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| plane_id | int(11) | NO | MUL | NULL | |
| pilot_id | int(11) | NO | MUL | NULL | |
| flight_date | date | NO | | NULL | |
| departure | time | NO | | NULL | |
| arrival | time | NO | | NULL | |
| origination | enum('STT','STX') | YES | | NULL | |
| destination | enum('STT','STX') | YES | | NULL | |
| price | float(6,2) | NO | | NULL | |
+-------------+-------------------+------+-----+---------+-- --------------+

mysql> describe Planes;
+--------------+-------------+------+-----+---------+------- ---------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+------- ---------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| in_service | tinyint(1) | NO | | 1 | |
| capacity | tinyint(2) | NO | | NULL | |
| total_weight | int(6) | NO | | NULL | |
+--------------+-------------+------+-----+---------+------- ---------+

My goal is to exclude results in which in_service !=3D1; however, the filte=
r
isn't working. Please advise.
TIA,
Victor

This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f you have received this e-mail by mistake and delete this e-mail from your=
system. E-mail transmission cannot be guaranteed to be secure or error-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate or incomplete, or contain viruses. The sender therefore does not accept=
liability for any loss or damage caused by viruses or errors or omissions =
in the contents of this message, which arise as a result of e-mail transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Join Problem

am 16.08.2010 20:53:21 von andrew.2.moore

Review your join type.
________________________________________
From: ext Gavin Towey [gtowey@ffn.com]
Sent: 16 August 2010 19:36
To: Victor Subervi; mysql@lists.mysql.com
Subject: RE: Join Problem

What do you mean by "not working?" What results do you get?

-----Original Message-----
From: Victor Subervi [mailto:victorsubervi@gmail.com]
Sent: Monday, August 16, 2010 6:59 AM
To: mysql@lists.mysql.com
Subject: Join Problem

Hi;
I have this code:

select f.id from Flights f join Planes p where f.plane_id=3Dp.id and
p.in_service=3D1

mysql> describe Flights;
+-------------+-------------------+------+-----+---------+-- --------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+---------+-- --------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| plane_id | int(11) | NO | MUL | NULL | |
| pilot_id | int(11) | NO | MUL | NULL | |
| flight_date | date | NO | | NULL | |
| departure | time | NO | | NULL | |
| arrival | time | NO | | NULL | |
| origination | enum('STT','STX') | YES | | NULL | |
| destination | enum('STT','STX') | YES | | NULL | |
| price | float(6,2) | NO | | NULL | |
+-------------+-------------------+------+-----+---------+-- --------------+

mysql> describe Planes;
+--------------+-------------+------+-----+---------+------- ---------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+------- ---------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| in_service | tinyint(1) | NO | | 1 | |
| capacity | tinyint(2) | NO | | NULL | |
| total_weight | int(6) | NO | | NULL | |
+--------------+-------------+------+-----+---------+------- ---------+

My goal is to exclude results in which in_service !=3D1; however, the filte=
r
isn't working. Please advise.
TIA,
Victor

This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f you have received this e-mail by mistake and delete this e-mail from your=
system. E-mail transmission cannot be guaranteed to be secure or error-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate or incomplete, or contain viruses. The sender therefore does not accept=
liability for any loss or damage caused by viruses or errors or omissions =
in the contents of this message, which arise as a result of e-mail transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dandrew.2.moore@noki=
a.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg