----- Original Message -----
From: "Scott Haneda" <[EMAIL PROTECTED]>
To: "MySql" <[email protected]>
Sent: Sunday, November 27, 2005 6:03 AM
Subject: Select returning more than I want
I am not sure why this is, perhaps some join I am doing wrong. I am
getting
back 2 results, or records, identical to each other, where I only want one
record.
SELECT o.id, o.s_first_name, o.s_last_name, u.email
FROM orders AS o
INNER JOIN users AS u
ON (o.user_id = u.id)
INNER JOIN order_items AS i
ON (i.order_id = o.id)
INNER JOIN products AS p
ON (i.product_id = p.id)
WHERE o.status IN ('pre-order', 'delayed')
AND (o.delayed_ship_date <= DATE_ADD(NOW(), INTERVAL 1 DAY)
OR o.delayed_ship_date = '0000-00-00')
AND o.authnet_failures < 5
AND o.id NOT IN (0)
AND o.authnet_status IN ('empty', 'failed')
Is it possible that one of your tables does not have a primary key defined
on it? That would be the most likely cause of the problem in my experience.
Check your table definitions and if any of the them is missing a PRIMARY KEY
clause, that's your likely culprit.
The other possibility that comes to mind is that one or more of your tables
has a multi-column key but that you are only joining on part of the key. In
that case, you could also get duplicate rows. Review each and every join and
make sure you are joining on all of the appropriate columns.
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 25/11/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
