CODEWITHSUNDEEP
mysql
Joshua Dannemann
Joshua Dannemann

Reputation: 2080

Missing Data in MySQL View

I have a view set up in a MySQL database. The view references several related tables and joins them together. Here is a select statement to illustrate as an example:

    SELECT
    orders.id,
    orders.`name`,
    orders.total,
    line_items.id,
    line_items.order_id,
    line_items.sub_total,
    order_fees.id,
    order_fees.order_id,
    order_fees.`name`,
    order_fees.fee
    FROM
    orders
    INNER JOIN line_items ON line_items.order_id = orders.id
    INNER JOIN order_fees ON order_fees.order_id = orders.id

The problem I am encountering is that an order may or may not have an additional fee associated to it. What appears to happen is that when no records exist for a given order in the order_fees table, then the select statement will not return the order.

How do I set this up so that all orders are returned from the query regardless of whether or not associated records exist?

Upvotes: 0

Views: 281

Answers (2)

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

User Left outer join instead of Inner join. When you do inner join it will return only those record which are available in both table. If you use Left outer join then it will return all records from left table.

Upvotes: 1

Jens
Jens

Reputation: 69460

Use left join instead of inner join:

SELECT
    orders.id,
    orders.`name`,
    orders.total,
    line_items.id,
    line_items.order_id,
    line_items.sub_total,
    order_fees.id,
    order_fees.order_id,
    order_fees.`name`,
    order_fees.fee
    FROM
    orders
    Left JOIN line_items ON line_items.order_id = orders.id
    Left JOIN order_fees ON order_fees.order_id = orders.id

Upvotes: 1

Related Questions