[BNM] mysql query quandary

[Rob Curle]

cool... and how would i filter the returned recordset further for a  
members search page...  for say, firstname LIKE 'rob%' ?  i've never  
used unions before.

r.

On 2 Dec 2008, at 15:08, Alastair James wrote:

> (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
>