Odtnhj

I'm trying to build an SQL query where I grab one table's information (WHERE shops.shop_domain = X) along with the COUNT of the customers table WHERE customers.product_id = 4242451.

The shops table DOES NOT have product.id in it, but the customers table DOES HAVE the shop_domain in it, hence my attempt to do some sort of join.

I essentially want to return the following:

• shops.id


• shops.name


• shops.shop_domain


• 
  COUNT OF CUSTOMERS WHERE customers.product_id = '4242451'

Here is my not so lovely attempt at the query.
I think I have the idea right (maybe...) but I can't wrap my head around building this query.

SELECT shops.id, shops.name, shops.shop_domain, COUNT(customers.customer_id) 
FROM shops 
LEFT JOIN customers ON shops.shop_domain = customers.shop_domain 
WHERE shops.shop_domain = 'myshop.com' AND 
 customers.product_id = '4242451' 
GROUP BY shops.shop_id

Relevant database schemas:

shops:
id, name, shop_domain

customers: 
id, name, product_id, shop_domain

You are close. The condition on customers needs to go in the ON clause, because this is a LEFT JOIN and customers is the second table:

SELECT s.id, s.name, s.shop_domain, COUNT(c.customer_id)
FROM shops s LEFT JOIN
 customers c
 ON s.shop_domain = c.shop_domain AND c.product_id = '4242451'
WHERE s.shop_domain = 'myshop.com'
GROUP BY s.id, s.name, s.shop_domain;

I am also inclined to include all three columns in the GROUP BY, although Postgres (and ANSI/ISO standards) are happy with just id if it is declared as the primary key in the table.

A correlated subquery should be substantially cheaper (and simpler) for the purpose:

SELECT id, name, shop_domain
 , (SELECT count(*)
 FROM customers
 WHERE shop_domain = s.shop_domain
 AND product_id = 4242451) AS special_count
FROM shops s
WHERE shop_domain = 'myshop.com';

This way you only need to aggregate in the subquery, and need not worry about undesired effects on the outer query.

Assuming product_id is a numeric data type, so I use a numeric literal (4242451) instead of a string literal '4242451' - which might cause problems otherwise.