Re: [PHP-DB] Cross reference query help

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/