Conditional INNER JOIN

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_referen ce =
shopping_cart_sales_shipping_address.reference

otherwise the shipping address is the same as the billing address.
Suggestions?

Ron


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Conditional INNER JOIN

------=_Part_22305_4134471.1211672425573
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 5/24/08, Ron Piggott wrote:
>
> 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_referen ce =
> shopping_cart_sales_shipping_address.reference
>
> otherwise the shipping address is the same as the billing address.
> Suggestions?
>
> Ron
>
>
>
>
Ron,

you can't do conditional joins but you can use the CASE WHEN THEN structure
to handle what you need. Check out the mysql docs for that



--

Bastien

Cat, the other other white meat

------=_Part_22305_4134471.1211672425573--

Re: Conditional INNER JOIN

This still isn't working; am I close?

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 ) CASE
shopping_cart_sales_shipping_address.same_as_customer WHEN
shopping_cart_sales_shipping_address.same_as_customer = '0' THEN INNER
JOIN shopping_cart_sales_billing_address ON
shopping_cart_sales_billing_address.shipping_address_referen ce =
shopping_cart_sales_shipping_address.reference

On Sat, 2008-05-24 at 19:40 -0400, Bastien Koert wrote:
>
>
> On 5/24/08, Ron Piggott wrote:
> 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_referen ce
> =
> shopping_cart_sales_shipping_address.reference
>
> otherwise the shipping address is the same as the billing
> address.
> Suggestions?
>
> Ron
>
>
>
>
> Ron,
>
> you can't do conditional joins but you can use the CASE WHEN THEN
> structure to handle what you need. Check out the mysql docs for that
>
>
>
> --
>
> Bastien
>
> Cat, the other other white meat


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Conditional INNER JOIN

------=_Part_22454_20102360.1211680043091
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 5/24/08, Ron Piggott wrote:
>
>
> This still isn't working; am I close?
>
> 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 ) CASE
> shopping_cart_sales_shipping_address.same_as_customer WHEN
> shopping_cart_sales_shipping_address.same_as_customer = '0' THEN INNER
> JOIN shopping_cart_sales_billing_address ON
> shopping_cart_sales_billing_address.shipping_address_referen ce =
> shopping_cart_sales_shipping_address.reference
>
> On Sat, 2008-05-24 at 19:40 -0400, Bastien Koert wrote:
> >
> >
> > On 5/24/08, Ron Piggott wrote:
> > 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_referen ce
> > =
> > shopping_cart_sales_shipping_address.reference
> >
> > otherwise the shipping address is the same as the billing
> > address.
> > Suggestions?
> >
> > Ron
> >
> >
> >
> >
> > Ron,
> >
> > you can't do conditional joins but you can use the CASE WHEN THEN
> > structure to handle what you need. Check out the mysql docs for that
> >
> >
> >
> > --
> >
> > Bastien
> >
> > Cat, the other other white meat
>
> http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

as mentioned you can't include tables conditionally, you can only handle a
columns data with this

but i think the real issue here is that if the user checks the 'same as
address' box, you should just populate the table with the same data,
therefore allowing the sql to be consistent


--

Bastien

Cat, the other other white meat

------=_Part_22454_20102360.1211680043091--

Re: Conditional INNER JOIN

2008/5/24 Ron Piggott :
> 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_referen ce =
> 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_referen ce =
shopping_cart_sales_shipping_address.reference
WHERE
shopping_cart_sales_shipping_address.same_as_customer = 0
)

Hope it helps


Martin

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php