Need help with joining a query

Databases
1

I am writing a query that will help identify a web visitor. We are trying to tie offline phone sales to the web traffic of the actual caller so we can measure the effectiveness of our online advertising. We are geo-coding visitor IP addresses to figure out where they’re visiting from. Then, when someone calls to place an order, we are looking at the visitors to the site that came from their location. To further reduce the field of potential visitors, we also want to order the results by the number of matching products in their cart that they viewed while on the site.

Our tables are set up as follows:

support_ids:
id (primary key)
client_id
latitude
longitudehttps://www.sitepoint.com/community/c/databases

support_id_products
id (tied to the support_id above)
products_id

When we query the database, we have a list of products in the user’s cart. We want to find the users who are within 30 miles of the phone caller’s zip code ordered by the number of matching products that were viewed that match what’s in the cart. So far, we have the query working that returns users within 30 miles and the query that counts the number of like products. We’re trying to figure out how to combine the results from these two queries and order the results by the number of product matches.

SELECT 
  id
  , latitude
  , longitude
  , client_id
FROM support_ids
  WHERE latitude BETWEEN $minLat AND $maxLatAND longitude BETWEEN $minLon AND $maxLon



SELECT 
  id
  , COUNT(products_id) as prod_count 
FROM support_id_products 
WHERE 
  products_id IN (32051, 29988, 30331) 
GROUP BY id

How do I combine those two queries? This is my latest attempt:

SELECT COUNT(sp.products_id) as prod_count, s.id, s.city, s.state, s.zip, s.country, s.latitude, s.longitude, s.support_id, s.client_id
        FROM support_ids as s
        WHERE latitude Between $minLat And $maxLat
          AND longitude Between $minLon And $maxLon
        INNER JOIN
        support_id_products as sp 
        ON sp.products_id IN (32051, 29988, 30331) GROUP BY sp.id

Thanks!

2

SELECT s.id , s.latitude , s.longitude , s.client_id , COALESCE(p.prod_count,0) AS prod_count FROM support_ids AS s LEFT OUTER JOIN ( SELECT id , COUNT(products_id) AS prod_count FROM support_id_products WHERE products_id IN (32051, 29988, 30331) GROUP BY id ) AS p ON p.id = s.id WHERE s.latitude BETWEEN $minLat AND $maxLat AND s.longitude BETWEEN $minLon AND $maxLon

3

Works like a champ! Thank’s Rudy.

4

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.