[BNM] mysql query quandary

[Alastair James]

(note, I may be using some mysql syntax by default / mistake)

Well if you change the LFT JOINS to INNER JOINS, each query will return
mutually exclusive members (i.e. a member is only returned by the
institution query OR the personal query) you can just UNION them together.
Note we also have to add fields to the personal query to make it have the
same column. This can be achieved by adding '"" AS institution_name' etc...

(
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
)
UNION
(
SELECT m.firstname, m.lastname, m.email, "" AS department, ""
AS institution_name, 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
)

Al

2008/12/2 Rob Curle <robcurle at mac.com>

> 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.
>
>
>
>
>
>
> --
>
> BNM Subscribe/Unsubscribe:
> http://www.brightonnewmedia.org/options/bnmlist
>
> BNM powered by Wessex Networks:
> http://www.wessexnetworks.com
>



-- 
Dr Alastair James
CTO James Publishing Ltd.

www.worldreviewer.com
Winner Yahoo! Finds of the Year
WINNER Travolution Awards Best New Online Travel Company 2008
"In a market increasingly crowded with new content and experience-led sites,
Worldreviewer stands out a mile. It has used exclusive editorial and
excellent tools to wonderful effect and ensured its commercial partnerships
are relevant to the target market."

Blogs: onewheeledbicycle.com, traveltelegraph.com

"Utinam logica falsa tuam philosophiam totam suffodiant!"