Prev: php, session_set_save_handler & disappearing session vars
Next: upload multiple files and rename wih time()
From: "Ron Piggott" on 6 Feb 2010 10:33

The following is a query I am working on today. I need help tweaking on it.

What I am trying to get for results from the query are where there are
rows in either table, not all 3 tables linked together.

In other words I am trying to INNER JOIN these two pairs of tables for
possible results:

`store_product_profile` and `paypal_cart_info`

OR

`store_product_profile` and `mail_order_cart`

SELECT `store_product_profile`.`product_name`
FROM (
`store_product_profile`
INNER JOIN `paypal_cart_info` ON `store_product_profile`.`reference` =
`paypal_cart_info`.`itemnumber`
)
INNER JOIN `mail_order_cart` ON `store_product_profile`.`reference` =
`mail_order_cart`.`store_product_profile_reference`
WHERE `store_product_profile`.`discontinued` =0
AND `store_product_profile`.`reference` =7

Finally I need help with the GROUP BY syntax also. I only want the name
of the product once, not if it was in several previous purchases.

Any help is appreciated.

Ron

From: Chris on 7 Feb 2010 21:13
Ron Piggott wrote:
> The following is a query I am working on today. I need help tweaking on it.
>
> What I am trying to get for results from the query are where there are
> rows in either table, not all 3 tables linked together.
>
> In other words I am trying to INNER JOIN these two pairs of tables for
> possible results:
>
> `store_product_profile` and `paypal_cart_info`
>
> OR
>
> `store_product_profile` and `mail_order_cart`
>
> SELECT `store_product_profile`.`product_name`
> FROM (
> `store_product_profile`
> INNER JOIN `paypal_cart_info` ON `store_product_profile`.`reference` =
> `paypal_cart_info`.`itemnumber`
> )
> INNER JOIN `mail_order_cart` ON `store_product_profile`.`reference` =
> `mail_order_cart`.`store_product_profile_reference`
> WHERE `store_product_profile`.`discontinued` =0
> AND `store_product_profile`.`reference` =7
>
> Finally I need help with the GROUP BY syntax also. I only want the name
> of the product once, not if it was in several previous purchases.
>
> Any help is appreciated.

So a product is either in the paypal_cart_info table or it's in the
mail_order_cart table ?

Though maybe this is a bad example - it's looking for reference=7 - you
can check for that in your other tables since it seems to be a foreign
key of some sort I'm guessing.

You could use a union (or union all is probably better in this case) to
bring them together.


select itemnumber
from paypal_cart_info
where itemnumber=7
union all
select store_product_profile_reference
from mail_order_cart
where store_product_profile_reference=7

put that into your other query and add your group by:

select product_name
from store_product_profile
where
reference in
(
select itemnumber
from paypal_cart_info
where itemnumber=7
union all
select store_product_profile_reference
from mail_order_cart
where store_product_profile_reference=7
)
and
discontinued=0
group by
product_name;


If you put the check for '7' in the paypal and mail_order_cart tables
it'll be faster than putting it in the bigger query.

The smaller subquery will return less results (I'm guessing 0,1, or 2
results) and therefore the discontinued check will be quicker and so
will the group by.

If you don't put the check for '7' in the subquery, then mysql will have
to go through *all* entries in paypal_cart_info and mail_order_cart
(which may or may not be big tables), then make sure there is a
reference entry in product_profile (which could be a pretty big table)
and finally at the end of all that, check if reference '7' is in that
list and it's not discontinued (then the group by etc).

So in general, the further down the query chain you can put your where
clauses to reduce the number of rows returned, the better.

--
Postgresql & php tutorials
http://www.designmagick.com/

 | 
Pages: 1
Prev: php, session_set_save_handler & disappearing session vars
Next: upload multiple files and rename wih time()