MySQL查询：获取同订单下6-12岁儿童及主联系人手机号

Got it, let's work through this problem step by step. Your original query only pulls child records because you're not linking back to the primary contact in the same event_attendee table. Here's how to fix this, plus some corrections to your original logic:

First, Fixes to Your Original Query Issues #

• Invalid date format: '2020-01-2014' is not a valid MySQL date string—you probably meant something like '2014-01-01'.
• Wrong field name: Your table uses dob for date of birth, but your query references date_of_birth.
• Unnecessary/incorrect GROUP BY: event_order_id doesn't exist in your table, and grouping isn't needed here since we're matching each child to their primary contact directly.

Correct Query Using Self-Join #

The best approach here is to self-join the event_attendee table: one instance for the child records, another for the primary contact linked to the same order_id.

SELECT
  child.id AS child_id,
  child.order_id,
  child.f_name AS child_first_name,
  child.l_name AS child_last_name,
  child.dob AS child_date_of_birth,
  primary_contact.phone AS primary_contact_phone
FROM event_attendee child
-- Link to event_order to filter by event
JOIN event_order eo ON child.order_id = eo.id
-- Link to event to target the specific event
JOIN event e ON eo.event_id = e.id
-- Self-join to get the primary contact for the same order
JOIN event_attendee primary_contact 
  ON child.order_id = primary_contact.order_id 
  AND primary_contact.relation = 'primary'
WHERE
  e.id = '0d323c3a-33f3-4583-8b10-65100403edc2'
  -- Filter for child records (relation = 'son')
  AND child.relation = 'son'
  -- Calculate age dynamically (6-12 years old) instead of hardcoding dates
  AND TIMESTAMPDIFF(YEAR, child.dob, CURDATE()) BETWEEN 6 AND 12;

Key Explanations #

1. Self-Join: We use two aliases (child and primary_contact) for the same table to fetch both child and primary contact records that share the same order_id.
2. Dynamic Age Calculation: TIMESTAMPDIFF(YEAR, child.dob, CURDATE()) calculates the exact age of each child, so you don't have to update the date range manually as time passes.
3. Optional: Handle Missing Primary Contacts: If some orders might not have a primary contact (unlikely in most business cases), replace JOIN with LEFT JOIN for the primary_contact table—this will keep child records even if no primary contact exists, with primary_contact_phone showing as NULL.

Alternative: Subquery for Primary Contact Phone #

If you prefer using a subquery instead of a self-join, this works too:

SELECT
  ea.id AS child_id,
  ea.order_id,
  ea.f_name AS child_first_name,
  ea.l_name AS child_last_name,
  ea.dob AS child_date_of_birth,
  -- Subquery to pull the primary contact's phone for the same order_id
  (SELECT phone FROM event_attendee WHERE order_id = ea.order_id AND relation = 'primary') AS primary_contact_phone
FROM event_attendee ea
JOIN event_order eo ON ea.order_id = eo.id
JOIN event e ON eo.event_id = e.id
WHERE
  e.id = '0d323c3a-33f3-4583-8b10-65100403edc2'
  AND ea.relation = 'son'
  AND TIMESTAMPDIFF(YEAR, ea.dob, CURDATE()) BETWEEN 6 AND 12;

Both approaches will give you the child info you need, plus the associated primary contact's phone number.

内容的提问来源于stack exchange，提问作者user13532287