[BNM] mysql query quandary

['Alex' Bridge]

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.