Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    2

    Help please with a complex SQL statement

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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    classic example of a LEFT OUTER JOIN
    Code:
    SELECT m.member_id 
         , c.contact_id 
         , c.email 
         , c.phone 
         , c.Name 
         , a.alert_type 
      FROM members AS m
    INNER
      JOIN contacts AS c
        ON c.member_id  = m.member_id 
    LEFT OUTER
      JOIN alerts AS a
        ON a.contact_id = c.contact_id
       AND a.alert_type = 'local'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2011
    Posts
    2

    Here is the solution.

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •