troubleshooting Question

how do I fix this COUNT query?

Avatar of bitt3n
bitt3n asked on
MySQL Server
5 Comments1 Solution248 ViewsLast Modified:
I have four tables:

shops (shop,shop_id), products(product_id,date), sex(sex,product_id), users(user_id,prior_login)

shops.shop and shops.shop_id values are unique

I wish to COUNT the number of distinct shops for which at least one row in the products table exists such that shops.shop_id=products.shop_id, sex.product_id=products.product_id, products.date >= users.prior_login, users.user_id=22, and sex.sex=0.

Right now I am trying:

SELECT COUNT (shop_id) AS total_shops FROM shops
WHERE EXISTS (
SELECT * FROM
products, sex WHERE
shops.shop_id=products.shop_id AND
sex.product_id=products.product_id AND
sex.sex=0 AND
(products.date >= (SELECT
prior_login FROM
users WHERE
user_id = 22)))

This produces a syntax error. What am I doing wrong?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros