Need help with a complex MySQL query_问答_开发者

I'm struggling to get a final result set for a 3 table hierarchical 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 ->   
                                                             -> '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开发者_JAVA百科.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?  

--
-- Table structure for table `contacts`
--

CREATE TABLE IF NOT EXISTS `contacts` (
  `contact_id` int(12) NOT NULL AUTO_INCREMENT,
  `member_id` int(12) NOT NULL,
  `email` varchar(30) NOT NULL,
  `phone` varchar(15) NOT NULL,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`contact_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ;

--
-- Dumping data for table `contacts`
--

INSERT INTO `contacts` (`contact_id`, `member_id`, `email`, `phone`, `name`) VALUES
(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', '987-654-3210', 'Dan'),
(5, 2, 'e@gmail.com', '', 'Ed'),
(6, 1, 'f@gmail.com', '', 'Fran'),
(7, 2, 'h@gmail.com', '234-567-8901', 'Hans'),
(8, 3, 'i@gmail.com', '', 'Ida'),
(9, 1, 'g@gmail.com', '', 'Greg'),
(10, 1, 'j@gmail.com', '456-789-0123', 'Jeff'),
(11, 2, 'k@gmail.com', '945-555-1212 ', 'Karl'),
(12, 3, 'l@gmail.com', '', 'Leo');

CREATE TABLE IF NOT EXISTS `contacts_selected` (
  `alert_id` int(12) NOT NULL AUTO_INCREMENT,
  `contact_id` int(12) NOT NULL,
  `alert_type` varchar(6) NOT NULL,
  PRIMARY KEY (`alert_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;

--
-- Dumping data for table `contacts_selected`
--

INSERT INTO `contacts_selected` (`alert_id`, `contact_id`, `alert_type`) VALUES
(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'),
(11, 1, 'nation');

CREATE TABLE IF NOT EXISTS `alert_types` (
  `alert_type` varchar(6) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `alert_types`
--

INSERT INTO `alert_types` (`alert_type`) VALUES
('local'),
('state'),
('nation');


SOLUTION:
$alert_type = 'local';
// choices are local, state, nation
//
SELECT c.contact_id, c.member_id, c.email, c.phone, c.desc, s.alert_type 
FROM contact c
    LEFT JOIN contact_select s 
    ON c.contact_id = s.contact_id
WHERE c.member_id = 1 AND c.contact_id NOT IN
(SELECT cs.contact_id FROM contact_select cs WHERE cs.alert_type = '$alert_type')
GROUP BY c.contact_id 
UNION
SELECT * FROM 
    (SELECT c.contact_id, c.member_id, c.email, c.phone, c.desc, s.alert_type 
    FROM contact c
    LEFT JOIN contact_select s 
    ON c.contact_id = s.contact_id
    WHERE c.member_id = 1 
    AND s.contact_id 
    IN (SELECT cs.contact_id FROM contact_select cs WHERE cs.alert_type = '$alert_type')) z
WHERE z.alert_type = '$alert_type'

This should give you your desired output.

SELECT C.member_id, C.contact_id, C.email, C.phone, C.name, S.alert_type 
FROM CONTACTS C 
    LEFT OUTER JOIN CONTACTS_SELECTED S
        ON C.contact_id = S.contact_id 
WHERE member_id = 1

select member_id, cs.contract_id, c.email, c.phone, c.name, cs.alert_type 
FROM contact c
LEFT JOIN contact_selected cs on cs.contact_id = c.contact_id
WHERE c.member_id = 1

Not sure i understand well what do you mean but maybe you're looking for thiS?

I would try this:

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`

However, two points: One, it's not clear to me how you want to narrow the result set to select only one of the alert types. Two, your sample data and your insert statments contain slightly different data. That's not a problem, but it is a little confusing at first.