[BNM] mysql query quandary

[Rob Curle]

cheers alex... this works!   i've been staring at this for ages...  
wood from the trees type thing i reckon.

r.

On 2 Dec 2008, at 15:15, 'Alex' Bridge wrote:

> Hi Rob,
>
> If you move the 'WHERE mia.primary_address=1' into the JOIN, i.e:
>
> LEFT OUTER JOIN membersInstitutionAddresses AS mia ON
> (m.id=mia.member_id AND mia.primary_address=1)
>
>
> and do the same for mpa, you should find that those two joins are no
> longer mutually exclusive and can be combined into a single query.  
> Then
> you'll just need to check which of the columns from mia or mpa are
> populated to get the address.
>
> I think.
>
> Cheers,
>
> Alex.
>
>
>
> Also sprach Rob Curle:
>> hi everyone,
>>
>> i'm having trouble trying to combine 2 mysql queries into 1.  i need
>> to search a members list (by firstname, lastname, email, etc) and
>> return the members details and primary address.  members can have
>> several addresses stored in 2 tables for work and personal addresses,
>> but only one will be marked as their primary address.
>> has anyone got any suggestions, maybe its possible with subqueries  
>> and
>> unions, but i'm stumped!
>>
>> heres a trimmed down version of the db schema: http://www.twelvenoon.co.uk/schema.html
>>
>> // return work addresses
>>
>> SELECT m.firstname, m.lastname, m.email, mia.department,
>> i.institution_name, i.street, i.city, i.postcode, c.country_name
>> FROM members AS m
>> 	LEFT OUTER JOIN membersInstitutionAddresses AS mia ON
>> m.id=mia.member_id
>> 	LEFT OUTER JOIN institutions AS i ON mia.institution_id=i.id
>> 	LEFT OUTER JOIN countries AS c ON i.country_id=c.id
>> WHERE mia.primary_address=1
>>
>>
>> // return perasonal addresses
>>
>> SELECT m.firstname, m.lastname, m.email, mpa.street, mpa.city,
>> mpa.postcode, c.country_name
>> FROM members AS m
>> 	LEFT OUTER JOIN membersPersonalAddresses AS mpa ON  
>> m.id=mpa.member_id
>> 	LEFT OUTER JOIN countries AS c ON mpa.country_id=c.id
>> WHERE mpa.primary_address=1
>>
>>
>> cheers,
>>
>> rob.
>>
>>
>>
>>
>>
>>
>>
>
>
> -- 
> Views or opinions expressed in this email are not necessarily
> those of the author and are not guaranteed fit for purpose.
> Not suitable for children under 36 months. May contain nuts.
>
> -- 
>
> BNM Subscribe/Unsubscribe:
> http://www.brightonnewmedia.org/options/bnmlist
>
> BNM powered by Wessex Networks:
> http://www.wessexnetworks.com