I'm struggling to get a final result set for a 3 table hieracrchical set of data.
Hopefully, the diagrams will indicate what I have and what I'm trying to do. Briefly,
my final result set (below) should easily allow me to define a dynamic number of
checkboxes in my web site, while also allowing me to define whether the boxes are
checked, all from within a single result set. I believe that since the data is
normalized, I should be able to get a single result set, but I can't get my head
wrapped around this on... Can anyone help??


TABLE A TABLE B TABLE C
MEMBER CONTACT ALERT
(pk)$member_id -> (pk)$contact_id -> (pk) $alert_id
(fk)$member_id (fk) $contact_id
$alert_type -> values are 'local', 'state', 'nation'

Example of my filter criteria is member_id = 1 AND alert_type = 'local'

* = results of filter member_id =1

TABLE MEMBERS A
+------------+----------+
|member_id | Name |
+------------+----------+
| 1 | Alan | *
| 2 | Brad |
| 3 | Doug |
| 4 | Flo |
+---------------------+

TABLE CONTACTS B
+--------------------------------------------------------------------+
| contact_id | member_id | email | phone | Name |
+------------+-------------+---------------+--------------+----------+
| 1 | 1 | a@gmail.com | | Alex | *
| 2 | 1 | b@gmail.com | 123-456-7890 | Bob | *
| 3 | 3 | c@gmail.com | | Cris |
| 4 | 1 | d@gmail.com | | Dan | *
| 5 | 2 | e@gmail.com | | Ed |
| 6 | 1 | f@gmail.com | | Fran | *
| 7 | 1 | g@gmail.com | 212-323-1111 | Greg | *
| 8 | 2 | h@gmail.com | | Hans |
| 9 | 3 | i@gmail.com | | Ida |
| 10 | 1 | j@gmail.com | 945-555-1212 | Jeff | *
| 11 | 2 | k@gmail.com | 945-555-1212 | Karl |
| 12 | 3 | l@gmail.com | | Leo |
+--------------------------------------------------------------------+

# = resutls of filter alert_type = 'local'
TABLE CONTACTS_SELECTED C
+-----------------------------------------+
| alert_id | contact_id | alert_type |
+------------+------------+---------------+
| 1 | 1 | local | * #
| 2 | 1 | state | *
| 3 | 3 | state |
| 4 | 5 | local |
| 5 | 5 | state |
| 6 | 6 | nation | *
| 7 | 7 | local | * #
| 8 | 8 | nation |
| 9 | 10 | local | *
| 10 | 12 | state |
+-------------------------+---------------+


REQUIRED OUTPUT
+------------------------------------------------------------------------------------+
|member_id | contract_id | email | phone | Name | alert_type |
+----------+--------------+---------------+--------------+----------+----------------+
| 1 | 1 | a@gmail.com | | Alex | local |
| 1 | 2 | b@gmail.com | 123-456-7890 | Bob | NULL |
| 1 | 4 | d@gmail.com | | Dan | NULL |
| 1 | 6 | f@gmail.com | | Fran | nation |
| 1 | 7 | g@gmail.com | 212-323-1111 | Greg | local |
| 1 | 10 | j@gmail.com | 945-555-1212 | Jeff | local |
+------------------------------------------------------------------------------------+

With this result set, I should be easily able to FOREACH my way through all 6 records
and create a checkbox for each record, and flag those records with 'local' as checked.
Can anyone help with setting up this complex query? Thanks in advance...

classic example of a LEFT OUTER JOIN

Thanks, but that was not the solution. It yielded all the records in the whole member table, including those which were not member_id =1. Plus all the alert_types showed up as NULL.

select `c`.`contact_id`, `member_id`, `email`, `phone`, `name`, `alert_type` from contacts `c`
left join contacts_selected `s` on `c`.`contact_id` = `s`.`contact_id`
where member_id=1
group by `c`.`contact_id`

It was the 'group-by' that I needed... duh! I was oh so close.


SOLVED