Hi Rob,
Try create a view based on the address book with only one address per
customer, e.g.
Create view single_address as
Select customers_id, min(address_book_id) as
address_book_id
From address_book
Group by customers_id
Then you can simply use a query to join customers, address_book and
single_address_book together; e.g.
Select c.*, a.*
From customers c, address_book a, single_address_book s
Where c.customers_id=a.customers_id and
a.customers_id=s.customers_id and a.address_book_id=s.address_book_id
p/s this syntax works for MySQL 5, I'm not sure about earlier versions.
Cheers,
Kuet-Fung
From: [email protected]
[mailto:[email protected]] On Behalf Of Robert
martin
Sent: Tuesday, 24 November 2009 12:18
To: NZ Borland Developers Group - Offtopic List
Subject: Re: [DUG-Offtopic] MySQL SQL question
Hi
Just tried a view (good idea) but I get
#1349 - View's SELECT contains a subquery in the FROM clause
So it looks like no sub selects in views. I had tried a stored
procedure but Got all sorts of errors, im not up on mySQL procedures.
Will keep looking into it :)
Rob
[email protected] wrote:
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
_______________________________________________
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
