how do I fix this COUNT query?

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)))

Open in new window


This produces a syntax error. What am I doing wrong?
bitt3nAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael CarrilloInformation Systems ManagerCommented:
Your not joining your tables correctly. But to provide the correct syntax, I need to know are you using MySQL or SQL Server?
0
Michael CarrilloInformation Systems ManagerCommented:
If you have SQL Server you can try:


SELECT COUNT (shop_id) AS total_shops
FROM shops
WHERE EXISTS(
SELECT *
FROM shops s inner join products p
on s.shop_id=p.shop_id
inner join sex x
on p.product_id=x.product_id
WHERE
x.sex=0 AND
(p.date >= (SELECT
prior_login FROM
users WHERE
user_id = 22)  ) )
0
bitt3nAuthor Commented:
I am using MySQL. Looking at your query, I'm confused why it is not necessary to correlate 'shops s' in the EXISTS query with 'shops' in the outer query. (I tried using your query in MySQL but presumably there is some syntax difference between SQL Server and MySQL that prevents it from working.)
0
bitt3nAuthor Commented:
it appears the problem was merely the space in 'COUNT (shop_id)', as when I remove this space, I get the expected result.

I'll leave this question open in case anyone wishes to suggest some other correction or improvement, but I assume the problem was just this stupid syntax mistake.
0
Michael CarrilloInformation Systems ManagerCommented:
Actually Shops is in the Exists query.  I am using an alias of 's'.  So instead of saying:
shops.shops_id
I can say
s.shops_id

Yes the syntax is different for MySLQ than for SQL Server.
 
Try:

SELECT COUNT (shop_id) AS total_shops
FROM shops
WHERE EXISTS (
SELECT *
FROM Products
Join Sex on  products.product_id=sex.product_id
Join Shops on products.shop_id=shops.shop_id
WHERE
sex.sex=0 AND
products.date >= (SELECT
prior_login FROM
users WHERE
user_id = 22)  
);
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.