SELECT t.*,
CASE WHEN t1.idbill IS NOT NULL THEN 'Combo' ELSE 'Non Combo' END
FROM Table t
LEFT JOIN (SELECT idbill
           FROM Table
           GROUP BY idbill
           HAVING COUNT(DISTINCT CASE WHEN productname IN ('apple','cherry') AND city <> 'miami' THEN productname ELSE NULL END) =2
          )t1
ON t1.idbill = t.idbill

quote:

---

Originally posted by suraz

##### Code Optimized #####

SELECT t.*,
CASE WHEN t1.idbill IS NOT NULL THEN 'Combo' ELSE 'Non Combo' END
FROM bills t
LEFT JOIN (SELECT idbill
FROM bills
WHERE city <>'miami'
GROUP BY idbill
HAVING COUNT(idbill)=2)t1 ON t1.idbill = t.idbill

---

quote:

---

Originally posted by sebastian11c

hi visakh16

its true the code from suraz its not as complete as yours for my requiremets, but he tried to help me too with his answer..
so im just saying thank you

thanks again visakh16 for your great reply , really helps to me your code

---

quote:

---

Originally posted by suraz

hi visakh16,

both queries returning the same result as sebastian11c's requirement and table/data structure.

Difference is only this:
Your query exactly meets the sebastian11c's requirement for the product "apple" and "cherry" and city<> 'miami' but it won't work when you want same result for other more products. Suppose you need to do same for strawberry then you add strawberry in your query i.e. every time need to change in query

You can do the same for thousand of products by using updated query and require no changes in query.

###################################################################
Thanks to All :)

---

quote:

---

Originally posted by visakh16

SELECT t.*,
CASE WHEN t1.idbill IS NOT NULL THEN 'Combo' ELSE 'Non Combo' END
FROM Table t
LEFT JOIN (SELECT idbill
           FROM Table
           GROUP BY idbill
           HAVING COUNT(DISTINCT CASE WHEN productname IN ('apple','cherry') AND city <> 'miami' THEN productname ELSE NULL END) =2
          )t1
ON t1.idbill = t.idbill

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by madhivanan

quote:

---

Originally posted by visakh16

SELECT t.*,
CASE WHEN t1.idbill IS NOT NULL THEN 'Combo' ELSE 'Non Combo' END
FROM Table t
LEFT JOIN (SELECT idbill
           FROM Table
           GROUP BY idbill
           HAVING COUNT(DISTINCT CASE WHEN productname IN ('apple','cherry') AND city <> 'miami' THEN productname ELSE NULL END) =2
          )t1
ON t1.idbill = t.idbill

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

If you use SUM(CASE WHEN ... THEN 1 ELSE 0 END)... you can avoid the unneccesary warning about NULL

Madhivanan

Failing to plan is Planning to fail

---