How to include Null records in Oracle query

Question

I have this query that returns what you would expect

    SELECT
e.event_date,
e.venue_id,
e.description     AS event,
t.price,
t.ticket_id,
bt.buyer_type_code,
bt.description    AS buyer_type,
btg.description   AS ticket_category,
SUM(sci.actual_amount) AS tax,
t.price + SUM(sci.actual_amount) AS revenue,
e.event_id,
coupon.coupon_code
    FROM
event e
INNER JOIN event_seat es ON e.event_id = es.event_id
INNER JOIN ticket t ON es.ticket_id = t.ticket_id
LEFT JOIN service_charge_item sci ON sci.ticket_id = t.ticket_id
INNER JOIN buyer_type bt ON t.buyer_type_id = bt.buyer_type_id
INNER JOIN buyer_type_group btg ON bt.buyer_type_group_id = btg.buyer_type_group_id
INNER JOIN coupon ON t.coupon_id = coupon.coupon_id
    WHERE
e.event_date > '1-JAN-2022'
AND e.description LIKE '%Hm%'
AND e.description NOT LIKE '%Mg%'
AND e.description NOT LIKE '%Gt%'
AND e.event_status_code = 'SAL'
    GROUP BY
e.event_date,
e.venue_id,
e.description,
t.price,
t.ticket_id,
bt.buyer_type_code,
bt.description,
btg.description,
e.event_id,
coupon.coupon_code

if i leave out the coupon_code references i get significantly more records. so it seems it is only returning records that have input in the coupon_code field. how do i return everything whether it has input or no input ?

Answer 1

You simply need a LEFT JOIN instead of INNER OIN -

SELECT
e.event_date,
e.venue_id,
e.description     AS event,
t.price,
t.ticket_id,
bt.buyer_type_code,
bt.description    AS buyer_type,
btg.description   AS ticket_category,
SUM(sci.actual_amount) AS tax,
t.price + SUM(sci.actual_amount) AS revenue,
e.event_id,
coupon.coupon_code
    FROM
event e
INNER JOIN event_seat es ON e.event_id = es.event_id
INNER JOIN ticket t ON es.ticket_id = t.ticket_id
LEFT JOIN service_charge_item sci ON sci.ticket_id = t.ticket_id
INNER JOIN buyer_type bt ON t.buyer_type_id = bt.buyer_type_id
INNER JOIN buyer_type_group btg ON bt.buyer_type_group_id = btg.buyer_type_group_id
LEFT JOIN coupon ON t.coupon_id = coupon.coupon_id   -- Change this to LEFT JOIN from INNER JOIN
    WHERE
e.event_date > '1-JAN-2022'
AND e.description LIKE '%Hm%'
AND e.description NOT LIKE '%Mg%'
AND e.description NOT LIKE '%Gt%'
AND e.event_status_code = 'SAL'
    GROUP BY
e.event_date,
e.venue_id,
e.description,
t.price,
t.ticket_id,
bt.buyer_type_code,
bt.description,
btg.description,
e.event_id,
coupon.coupon_code