Hi,
I work with a rel. 3.23.36 of MYSQL and I have found this problem:
I have two tables named t_payments and t_payment_lang; these are their
descriptions and data:
t_payments (description):
+-----------------+---------------+------+-----+---------+----------------
+
| Field | Type | Null | Key | Default | Extra
|
+-----------------+---------------+------+-----+---------+----------------
+
| pay_key | int(11) | | PRI | NULL | auto_increment
|
| pay_enabled | tinyint(1) | YES | | 0 |
|
| pay_fixed_comm | decimal(18,4) | YES | | 0.0000 |
|
| pay_perc_comm | decimal(18,4) | YES | | 0.0000 |
|
| pay_step_height | decimal(18,4) | YES | | 0.0000 |
|
| pay_step_width | decimal(18,4) | YES | | 0.0000 |
|
| pay_tax_id | int(11) | YES | | 1 |
|
| pay_default | tinyint(1) | YES | | NULL |
|
| pay_en_download | tinyint(1) | YES | | NULL |
|
+-----------------+---------------+------+-----+---------+----------------
+
t_payments (data, some fields)
+---------+-------------+----------------+-------------+-----------------+
| pay_key | pay_enabled | pay_fixed_comm | pay_default | pay_en_download |
+---------+-------------+----------------+-------------+-----------------+
| 1 | 1 | 0.0000 | 1 | 1 |
| 2 | 1 | 0.0000 | 0 | 1 |
| 3 | 0 | 0.0000 | 0 | NULL |
| 4 | 1 | 0.0000 | 1 | NULL |
+---------+-------------+----------------+-------------+-----------------+
t_payment_lang (description):
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| pyl_key | int(11) | | PRI | NULL | auto_increment |
| pyl_pay_id | int(11) | | | 0 | |
| pyl_lang_id | int(11) | | | 0 | |
| pyl_description | varchar(100) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
t_payment_lang (data)
+---------+------------+-------------+------------------+
| pyl_key | pyl_pay_id | pyl_lang_id | pyl_description |
+---------+------------+-------------+------------------+
| 1 | 1 | 1 | Cash on delivery |
| 17 | 2 | 1 | Credit Card |
| 18 | 3 | 1 | Credit Card 2 |
+---------+------------+-------------+------------------+
The result of this query:
SELECT pay_key, pyl_description, pay_en_download
FROM t_payments,t_payment_lang
WHERE t_payments.pay_key = t_payment_lang.pyl_pay_id
AND t_payment_lang.pyl_lang_id = 1
AND t_payments.pay_enabled <> 0;
is:
+---------+------------------+-----------------+
| pay_key | pyl_description | pay_en_download |
+---------+------------------+-----------------+
| 1 | Cash on delivery | NULL |
| 2 | Credit Card | NULL |
+---------+------------------+-----------------+
the correct (aspected!) result is:
+---------+------------------+-----------------+
| pay_key | pyl_description | pay_en_download |
+---------+------------------+-----------------+
| 1 | Cash on delivery | 1 |
| 2 | Credit Card | 1 |
+---------+------------------+-----------------+
I obtain the correct result if i write the same query in this way:
SELECT pay_key, pyl_description, pay_en_download
FROM t_payments LEFT JOIN t_payment_lang
ON t_payments.pay_key = t_payment_lang.pyl_pay_id
WHERE t_payment_lang.pyl_lang_id = 1
AND t_payments.pay_enabled <> 0;
or if I drop the column pay_fixed_comm column from table t_payments.
I don't understand why !
Thanks for any advice.
Luca
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
