Dear Sir
I Have 3 Tables
Product
Id Name
0001 Product 1
0002 Product 2
0003 Product 3
0004 Product 4
0005 Product 5
Application
0001 Application 1
0002 Application 2
0003 Application 3
0004 Application 4
0005 Application 5
Combined table of Product + Application
application id productid
0001 0001
0001 0002
0001 0003
0003 0001
0002 0003
I want to create query that will display
list of products with product count
I am listed the product list in order of application Count (i.e. number of applications)
using following query
SELECT DISTINCT a.productid, COUNT( a.applicationid ) AS appcount, b. *
FROM productapplication AS a, product b
WHERE a.productid = b.id
GROUP BY productid
ORDER BY appcount, a.productid
However I wish to display products with Application count =2 or Number of applications specified by user
pls help
I tried
SELECT DISTINCT a.productid, COUNT( a.applicationid ) AS appcount, b. *
FROM productapplication AS a, product b
WHERE a.productid = b.id and appcount =2
GROUP BY productid
ORDER BY appcount, a.productid
but getting error
#1054 - Unknown column 'appcount' in 'where clause'
please help