Get Count in One to Many relation in sql

问题:

here is the link of the related question Related question ,

Now i have a query which gives me the company name exactly as i want

QUERY

 SELECT  CASE WHEN COALESCE(b.totalCoupons, 0) > 3 THEN a.Name +'(Important) '
     WHEN IsHighPriority = 1 THEN a.Name +'(High Priority) '
    ELSE a.Name +''
END  AS CompanyName
 FROM    Company a
LEFT JOIN
(
    SELECT  Name, COUNT(*) totalCoupons
    FROM    Company
    GROUP   BY Name
) b ON a.name = b.name

Now i want that In the scenario of (important) we are testing that if company have more then 3 coupons then add (Important) infront of Company name but i want to do that if a company have more then 3 coupons where

RejectProcessed = 0 and ReviewVerify = 0 and isPublish = 0 and ForSupervisor = 0 

then i want to add important infront of that particular company name . So , what should i do .

Please feel free to ask if you need any detail .

Thanks in advance

回答1:

This query solved my problem

SELECT  CASE WHEN COALESCE(b.totalCoupons, 0) > 3 THEN company.Name +' (Important) '
        WHEN IsHighPriority = 1 THEN company.Name +' (High Priority) '
        ELSE company.Name +''
    END  AS CompanyName , company.id as Companyid
FROM    Company company
    left JOIN
    (
        SELECT  co.Name as coName, co.id as coid, COUNT(c.id) totalCoupons
        FROM    Company co, Coupon c
        where c.CompanyId = co.id   and c.RejectedProcessed = 1 and c.ReviewVerify = 0 and c.isPublish = 0 and c.ForSupervisor = 0 
        GROUP   BY co.Name, co.id
    ) b ON company.id = b.coid

回答2:

@user2193861, is Coupon present in left join with company table..?

LEFT JOIN
(
    SELECT  Name, COUNT(*) totalCoupons
    FROM    Coupon 
    GROUP   BY Name
) b ON a.name = b.name

回答3:

Use this..

SELECT CASE WHEN COALESCE(b.totalCoupons, 0) > 3 THEN a.Name +'(Important) '

WHEN IsHighPriority = 1 THEN a.Name +'(High Priority) '

ELSE a.Name +''

END AS CompanyName

FROM Company a

LEFT JOIN

(

SELECT  Name, COUNT(*) totalCoupons

FROM    Company C

WHERE

EXISTS(

         select 1 

         from coupon P

         where P.RejectProcessed = 0 and P.ReviewVerify = 0

         and P.isPublish = 0 and P.ForSupervisor = 0 

         and P.CompanyName = C.Name

       )

and in second query..

use WHERE P.RejectProcessed = 0 and P.ReviewVerify = 0

and P.isPublish = 0 and P.ForSupervisor = 0