Re: Cross reference query help

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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/


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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux