CODEWITHSUNDEEP
phpmysqlsqljoinsyntax
Edward
Edward

Reputation: 1883

SQL JOIN with date 'categories'

I am trying to create a query that groups members into children and adults:

mysql tickets:

|ticket_id|ticket_no|
|    1    | 123456  |

mysql members:

|member_id|ticket_no|  name  |    dob     |
|    1    | 123456  | edward | 2010-03-05 | //child
|    2    | 123456  | karen  | 1965-03-05 | //adult

html output:

|ticket_no|Adult Names|Children Names|
| 123456  |   karen   |    edward    |

I would like to do it with a join query really and output it in a loop as a row. This is just an example:

SELECT t.*, m.*
FROM (`tickets` t)
JOIN(`members` m)
    ON(m.`ticket_no` = t.`ticket_no`)
    (SELECT m.`name` FROM m WHERE m.`dob` BETWEEN {$cStart} AND {$cEnd}) as children
    (SELECT m.`name` FROM m WHERE m.`dob` BETWEEN {$aStart} AND {$aEnd}) as adults
ORDER BY t.`ticket_no`

Any help greatly appreciated.

Working SQL:

        SELECT t.*,
        CASE
            WHEN m.`dob`
                BETWEEN '{$datetime['start']}'
                AND '{$datetime['end']}'
            THEN m.`name`
        END AS ChildName
        FROM `tickets` t
        JOIN (`members` m)
            ON (m.`ticket_no` = t.`ticket_no`)
        ORDER BY t.`ticket_no`

Upvotes: 1

Views: 44

Answers (1)

Brave Soul
Brave Soul

Reputation: 3620

you can try this. Just pass appropriate date range in variables you are using

    SELECT t.ticket_no,
    case when m.`dob` BETWEEN {$aStart} AND {$aEnd} then m.name  end as AdultName,
 case when m.`dob` BETWEEN {$cStart} AND {$cEnd} then m.name end as ChildName
     FROM (`tickets` t)
    JOIN(`members` m)
        ON(m.`ticket_no` = t.`ticket_no`)
    ORDER BY t.`ticket_no`

Upvotes: 1

Related Questions