Oops, sorry, I was up too late writing that. I knew if I quickly typed in a
test query I would mess it up somewhere. sheesh. Here's the actual query:
SELECT ag_admin_cart.*, ag_paymethod.paymethod,
ag_paymethod.miva_module_id,
Merchant2_00000001_customer_dbf.sql_bill_fname,
Merchant2_00000001_customer_dbf.sql_bill_lname,
Merchant2_00000001_customer_dbf.sql_bill_comp,
Merchant2_00000001_customer_dbf.sql_bill_addr,
Merchant2_00000001_customer_dbf.sql_bill_city,
Merchant2_00000001_customer_dbf.sql_bill_state,
Merchant2_00000001_customer_dbf.sql_bill_zip,
Merchant2_00000001_customer_dbf.sql_bill_cntry,
Merchant2_00000001_customer_dbf.sql_bill_email,
Merchant2_00000001_customer_dbf.sql_bill_phone,
Merchant2_00000001_customer_dbf.sql_ship_fname,
Merchant2_00000001_customer_dbf.sql_ship_lname,
Merchant2_00000001_customer_dbf.sql_ship_comp,
Merchant2_00000001_customer_dbf.sql_ship_addr,
Merchant2_00000001_customer_dbf.sql_ship_city,
Merchant2_00000001_customer_dbf.sql_ship_state,
Merchant2_00000001_customer_dbf.sql_ship_zip,
Merchant2_00000001_customer_dbf.sql_ship_cntry,
Merchant2_00000001_customer_dbf.sql_ship_email,
Merchant2_00000001_customer_dbf.sql_ship_phone,
Merchant2_00000001_customer_dbf.sql_id,
Merchant2_00000001_customer_dbf.sql_recno,
ag_customer_extras.alternate_phone,
ag_customer_extras.address_line2,
ag_customer_extras.ship_address_line2,
ag_customer_extras.dietary_concerns
FROM ag_admin_cart
LEFT JOIN Merchant2_00000001_customer_dbf
ON ag_admin_cart.custid = Merchant2_00000001_customer_dbf.sql_recno
LEFT JOIN ag_customer_extras
ON ag_customer_extras.sql_cust_recno =
Merchant2_00000001_customer_dbf.sql_recno
LEFT JOIN ag_paymethod
ON ag_paymethod.paymethod_id = ag_admin_cart.paymethod_id
WHERE cartid = ".$_SESSION['cartid'];
Jenifer
----- Original Message -----
From: "Roger Baklund" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Cc: "Subscriptions" <[EMAIL PROTECTED]>
Sent: Monday, December 12, 2005 11:25 AM
Subject: Re: Joins on tables with funky data?
Subscriptions aka Jenifer wrote:
query: SELECT tblCustomer.*, tblNotes.note FROM tblCustomer LEFT
JOIN tblNotes ON tblCustomer.customerid = tblNotes.noteID WHERE
tblCustomer.customerid = 123
You are joining the customerid from the customer table to the noteID from
your notes table. You should join with tblNotes.customerID. If most
customers have a note, or at least all customers with a small customerid
hva notes, this error would manifest in behaviour like you described, as
only customers without a note would cause a difference in the integer
value of the keys noteid and customerid. Simpler put: it would seem to
work until you inserted a customer without a note, and additional
customers with notes after that.
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
