you could use Temp tables and put the sub selects into them, then join to the temp tables.
If you are using MSSQL, then you would definitly be better to do this in a stored proc.
(in MSSQL temp tables are defined with a # in front of the name. i.e. create table #tmp just in case you didnt know)
Jeremy
-------- Original Message --------
Subject: [DUG-Offtopic] MySQL SQL question
From: Robert martin <[email protected]>
Date: Tue, November 24, 2009 11:16 am
To: NZ Borland Developers Group - Offtopic List
<[email protected]>
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
