Help with query...

I'm hoping someone can point me in the right direction on how to get
what I want with this query.

SELECT c.customers_firstname, c.customers_lastname,
c.customers_email_address, ab.entry_street_address,
ab.entry_street_address_2, ab.entry_city, ab.entry_postcode,
z.zone_name as state
FROM customers c
LEFT JOIN address_book ab ON c.customers_id = ab.customers_id
LEFT JOIN customers_info ci ON c.customers_id = ci.customers_info_id
LEFT JOIN zones z ON ab.entry_zone_id=z.zone_id
WHERE c.customers_newsletter="1"
AND ci.customers_info_stores_id="10";

This query works except the fact that the address_book table might
have more that one entry for each customer. All I want is the first
entry since this will be the billing address added when the customer
signed up with us. How do I tell the query to only retrieve the first
address_book entry?

TIA
"If you love wealth more than liberty, the
tranquillity of servitude better than the
animating contest of freedom, depart from us in
peace. We ask not your counsel or your arms.
Crouch down and lick the hand that feeds you.
May your chains rest lightly upon you and may
posterity forget that you were our countrymen."
- Samuel Adams

Re: Help with query...

"MattMika" ???????/???????? ? ???????? ?????????:
news:9rmni2dkhju1eg8bcn1pqt9ktefcptp5i7@4ax.com...
> I'm hoping someone can point me in the right direction on how to get
> what I want with this query.
>
> SELECT c.customers_firstname, c.customers_lastname,
> c.customers_email_address, ab.entry_street_address,
> ab.entry_street_address_2, ab.entry_city, ab.entry_postcode,
> z.zone_name as state
> FROM customers c
> LEFT JOIN address_book ab ON c.customers_id = ab.customers_id
> LEFT JOIN customers_info ci ON c.customers_id = ci.customers_info_id
> LEFT JOIN zones z ON ab.entry_zone_id=z.zone_id
> WHERE c.customers_newsletter="1"
> AND ci.customers_info_stores_id="10";
>
> This query works except the fact that the address_book table might
> have more that one entry for each customer. All I want is the first
> entry since this will be the billing address added when the customer
> signed up with us. How do I tell the query to only retrieve the first
> address_book entry?
>
> TIA
> "If you love wealth more than liberty, the
> tranquillity of servitude better than the
> animating contest of freedom, depart from us in
> peace. We ask not your counsel or your arms.
> Crouch down and lick the hand that feeds you.
> May your chains rest lightly upon you and may
> posterity forget that you were our countrymen."
> - Samuel Adams

something like
ORDER column ASC LIMIT 1;

Re: Help with query...

"MattMika" wrote in message
news:9rmni2dkhju1eg8bcn1pqt9ktefcptp5i7@4ax.com...
> I'm hoping someone can point me in the right direction on how to get
> what I want with this query.
>
> SELECT c.customers_firstname, c.customers_lastname,
> c.customers_email_address, ab.entry_street_address,
> ab.entry_street_address_2, ab.entry_city, ab.entry_postcode,
> z.zone_name as state
> FROM customers c
> LEFT JOIN address_book ab ON c.customers_id = ab.customers_id
> LEFT JOIN customers_info ci ON c.customers_id = ci.customers_info_id
> LEFT JOIN zones z ON ab.entry_zone_id=z.zone_id
> WHERE c.customers_newsletter="1"
> AND ci.customers_info_stores_id="10";
>
> This query works except the fact that the address_book table might
> have more that one entry for each customer. All I want is the first
> entry since this will be the billing address added when the customer
> signed up with us. How do I tell the query to only retrieve the first
> address_book entry?
>
> TIA
> "If you love wealth more than liberty, the
> tranquillity of servitude better than the
> animating contest of freedom, depart from us in
> peace. We ask not your counsel or your arms.
> Crouch down and lick the hand that feeds you.
> May your chains rest lightly upon you and may
> posterity forget that you were our countrymen."
> - Samuel Adams


Try with GROUP BY
This should work if you use variables in this code (they don't change
frequently)

Re: Help with query...

MattMika wrote:

> I'm hoping someone can point me in the right direction on how to get
> what I want with this query.
>
> SELECT c.customers_firstname, c.customers_lastname,
> c.customers_email_address, ab.entry_street_address,
> ab.entry_street_address_2, ab.entry_city, ab.entry_postcode,
> z.zone_name as state
> FROM customers c
> LEFT JOIN address_book ab ON c.customers_id = ab.customers_id
> LEFT JOIN customers_info ci ON c.customers_id = ci.customers_info_id
> LEFT JOIN zones z ON ab.entry_zone_id=z.zone_id
> WHERE c.customers_newsletter="1"
> AND ci.customers_info_stores_id="10";
>
> This query works except the fact that the address_book table might
> have more that one entry for each customer. All I want is the first
> entry since this will be the billing address added when the customer
> signed up with us. How do I tell the query to only retrieve the first
> address_book entry?
>
> TIA

Well, as long as your address_book table has an incremental id column
for its primary index then you could use a variation of the query that
Strawberry posted here:

http://groups.google.co.uk/group/alt.php.sql/browse_frm/thre ad/456528317c40d8d1/ed022077f46cdec9?lnk=gst&q=strawberry&rn um=3#ed022077f46cdec9

You would need to reverse the less than comparison to get the first
occurance instead of the last one.