Re: [PHP-DB] Conditional INNER JOIN

2008/5/24 Ron Piggott <[email protected]>:
> Is it possible to do a conditional INNER JOIN ?
>
> So far I have
>
> SELECT * FROM ( shopping_cart_orders INNER JOIN
> shopping_cart_sales_shipping_address ON
> shopping_cart_orders.shipping_address_reference =
> shopping_cart_sales_shipping_address.reference )
>
> If shopping_cart_sales_shipping_address.same_as_customer has a value of
> "0" I need to
>
> INNER JOIN shopping_cart_sales_billing_address WHERE
> shopping_cart_sales_billing_address.shipping_address_reference =
> shopping_cart_sales_shipping_address.reference
>
> otherwise the shipping address is the same as the billing address.
> Suggestions?

If the fields of both queries are the same, you can use UNION to
collect all the records:

SELECT
  *
FROM
(
  SELECT * FROM
      shopping_cart_orders
    INNER JOIN
      shopping_cart_sales_shipping_address
    ON
      shopping_cart_orders.shipping_address_reference =
shopping_cart_sales_shipping_address.reference
  WHERE
    shopping_cart_sales_shipping_address.same_as_customer <> 0
)
UNION
(
  SELECT * FROM
      shopping_cart_orders
    INNER JOIN
      shopping_cart_sales_billing_address
    ON
      shopping_cart_sales_billing_address.shipping_address_reference =
shopping_cart_sales_shipping_address.reference
  WHERE
    shopping_cart_sales_shipping_address.same_as_customer = 0
)

Hope it helps


Martin