Use a view? Cheers, Kuet-Fung
-----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Robert martin Sent: Tuesday, 24 November 2009 11:16 To: NZ Borland Developers Group - Offtopic List Subject: [DUG-Offtopic] MySQL SQL question Hi I have a database (not designed by me) where there are two tables, customers and aaddress_book. A customer can have multiple entries in aaddress_book and aaddress_book has the customers_id Pk to link back into customers. What I waned was a list of customers and there first / primary address. unfortunately there is no field in the address table to indicate this. I came up with the following SQL SELECT c.customers_id, c.customers_firstname, c.customers_email_address, c.customers_telephone, c.customers_fax, a.entry_firstname, a.entry_street_address, a.entry_suburb, a.entry_postcode, a.entry_city, a.entry_state, a.entry_country_id FROM customers c JOIN ( SELECT * FROM address_book aa WHERE aa.address_book_id IN ( SELECT MIN( aaa.address_book_id ) FROM address_book aaa GROUP BY aaa.customers_id ) )a ON a.customers_id = c.customers_id WHERE c.customers_referral IS NULL ORDER BY c.customers_id; This gives the correct results and runs fine in phpmyAdmin (this db is web based). However when I run it in my PHP script using PDO as the db connection method it fails and continues to fail until I remove all subselects. I think this is related to PDO not knowing / working with sub selects. So my question is... Is there a way of doing this without sub selects ? (stored procedures ?) TIA Rob _______________________________________________ NZ Borland Developers Group Offtopic mailing list Post: [email protected] Admin: http://delphi.org.nz/mailman/listinfo/offtopic Unsubscribe: send an email to [email protected] with Subject: unsubscribe _______________________________________________ NZ Borland Developers Group Offtopic mailing list Post: [email protected] Admin: http://delphi.org.nz/mailman/listinfo/offtopic Unsubscribe: send an email to [email protected] with Subject: unsubscribe
