SQLTeam.com | Weblogs | Forums

Sql query help

mysql

#1

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


#2

Try this:

select a.id
      ,count(b.applicationid) as appcount
      ,b.name
  from product as a
       inner join productapplication as b
               on b.productid=a.id
 group by a.productid
         ,a.productname
 having count(b.applicationid)=2
 order by a.id
;

or this:

with cte(productid,appcount)
  as (select productid
            ,count(applicationid)
        from productapplication
       group by productid
       having count(b.applicationid)=2
     )
select a.productid
      ,a.appcount
      ,b.name
  from cte as a
       inner join product as b
               on b.id=a.productid
 order by a.productid
;

or this:

select a.productid
      ,a.appcount
      ,b.name
  from (select productid
              ,count(applicationid) as appcount
          from productapplication
         group by productid
         having count(b.applicationid)=2
       ) as a
       inner join product as b
               on b.id=a.productid
 order by a.productid
;

Ps.: please avoid implicit joins as this makes larger queries hard to read


#3

Most perfect answer...
Thanks a lot