Too Strange for Words

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

Hi;
Can someone please explain to me why Passengers.weight comes up null for the
last value sometimes, and other times it comes up with the value 155??

mysql> select p.id, c.id, p.name, p.weight, m.amount, p.round_trip,
c.first_name, c.middle_name, c.last_name, c.suffix, c.sex, p.confirmation,
m.type, m.payment, p.late_fee, p.late_fee_paid, m.discount from Passengers p
join Customers c join Money m on m.foreign_id=p.id and
m.customer_id=c.idwhere m.type="flight fee" and p.flight_id=1;
+----+----+------+--------+--------+------------+----------- -+-------------+-----------+--------+------+--------------+- -----------+---------+----------+---------------+----------+
| id | id | name | weight | amount | round_trip | first_name | middle_name |
last_name | suffix | sex | confirmation | type | payment | late_fee |
late_fee_paid | discount |
+----+----+------+--------+--------+------------+----------- -+-------------+-----------+--------+------+--------------+- -----------+---------+----------+---------------+----------+
| 1 | 1 | NULL | NULL | 70.00 | 0 | Adolph | NULL |
Hitler | NULL | Male | 123456 | flight fee | CC | no |
n/a | 0 |
| 2 | 1 | NULL | NULL | 70.00 | 0 | Adolph | NULL |
Hitler | NULL | Male | 987987987 | flight fee | cash | no |
n/a | 0 |
| 3 | 3 | NULL | NULL | 70.00 | 0 | Me | NULL |
Too | NULL | Male | 123456 | flight fee | CC | no |
n/a | 20 |
+----+----+------+--------+--------+------------+----------- -+-------------+-----------+--------+------+--------------+- -----------+---------+----------+---------------+----------+
3 rows in set (0.00 sec)

mysql> select * from Passengers;
+----+-----------+-------------+------+------+--------+----- --+------------+--------------+----------+---------------+
| id | flight_id | customer_id | name | sex | weight | price | round_trip |
confirmation | late_fee | late_fee_paid |
+----+-----------+-------------+------+------+--------+----- --+------------+--------------+----------+---------------+
| 1 | 1 | 1 | NULL | Male | NULL | 0.00 | 0 |
123456 | no | n/a |
| 2 | 1 | 1 | NULL | Male | NULL | 0.00 | 0 |
987987987 | no | n/a |
| 3 | 1 | 2 | NULL | Male | NULL | 0.00 | 0 |
123456 | no | n/a |
| 4 | 1 | 3 | NULL | Male | 155 | 70.00 | 0 |
123456 | no | n/a |
+----+-----------+-------------+------+------+--------+----- --+------------+--------------+----------+---------------+
4 rows in set (0.00 sec)

mysql> select p.weight from Passengers p;
+--------+
| weight |
+--------+
| NULL |
| NULL |
| NULL |
| 155 |
+--------+
4 rows in set (0.00 sec)

TIA,
Victor

--000e0cd6682a80f01504908d1977--

Re: Too Strange for Words

In the last episode (Sep 18), Victor Subervi said:
> Can someone please explain to me why Passengers.weight comes up null for
> the last value sometimes, and other times it comes up with the value 155??

Passengers id 1 through 3 have a weight of NULL in the Passengers table, and
passenger 4 has a weight of 155. Your first query ends up returning rows
for only matches passengers 1 through 3, so you will always get NULL in the
weight column. What were you expecting to see?

> mysql> select p.id, c.id, p.name, p.weight, m.amount, p.round_trip,
> c.first_name, c.middle_name, c.last_name, c.suffix, c.sex, p.confirmation,
> m.type, m.payment, p.late_fee, p.late_fee_paid, m.discount from Passengers p
> join Customers c join Money m on m.foreign_id=p.id and
> m.customer_id=c.idwhere m.type="flight fee" and p.flight_id=1;
> +----+----+------+--------+--------+------------+----------- -+-------------+-----------+--------+------+--------------+- -----------+---------+----------+---------------+----------+
> | id | id | name | weight | amount | round_trip | first_name | middle_name | last_name | suffix | sex | confirmation | type | payment | late_fee | late_fee_paid | discount |
> +----+----+------+--------+--------+------------+----------- -+-------------+-----------+--------+------+--------------+- -----------+---------+----------+---------------+----------+
> | 1 | 1 | NULL | NULL | 70.00 | 0 | Adolph | NULL | Hitler | NULL | Male | 123456 | flight fee | CC | no | n/a | 0 |
> | 2 | 1 | NULL | NULL | 70.00 | 0 | Adolph | NULL | Hitler | NULL | Male | 987987987 | flight fee | cash | no | n/a | 0 |
> | 3 | 3 | NULL | NULL | 70.00 | 0 | Me | NULL | Too | NULL | Male | 123456 | flight fee | CC | no | n/a | 20 |
> +----+----+------+--------+--------+------------+----------- -+-------------+-----------+--------+------+--------------+- -----------+---------+----------+---------------+----------+
> 3 rows in set (0.00 sec)
>
> mysql> select * from Passengers;
> +----+-----------+-------------+------+------+--------+----- --+------------+--------------+----------+---------------+
> | id | flight_id | customer_id | name | sex | weight | price | round_trip | confirmation | late_fee | late_fee_paid |
> +----+-----------+-------------+------+------+--------+----- --+------------+--------------+----------+---------------+
> | 1 | 1 | 1 | NULL | Male | NULL | 0.00 | 0 | 123456 | no | n/a |
> | 2 | 1 | 1 | NULL | Male | NULL | 0.00 | 0 | 987987987 | no | n/a |
> | 3 | 1 | 2 | NULL | Male | NULL | 0.00 | 0 | 123456 | no | n/a |
> | 4 | 1 | 3 | NULL | Male | 155 | 70.00 | 0 | 123456 | no | n/a |
> +----+-----------+-------------+------+------+--------+----- --+------------+--------------+----------+---------------+
> 4 rows in set (0.00 sec)
>
> mysql> select p.weight from Passengers p;
> +--------+
> | weight |
> +--------+
> | NULL |
> | NULL |
> | NULL |
> | 155 |
> +--------+
> 4 rows in set (0.00 sec)
>
> TIA,
> Victor

--
Dan Nelson
dnelson@allantgroup.com

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