Re : Sql Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Re : Sql Query

Author

Topic

dwarakk
Starting Member

3 Posts

Posted - 2010-02-11 : 23:51:35

I have table where the users has various products.For ex a User A may have product medical and dental Simillarly a User B May have product dental.I want to write a query such that when dental is selected only user who has dental should come(B) and user who has dental and other products should not come(A).

In order to summarize i should get user records who has only dental but not medical & dental.

User Product
A Medical
A Dental
B Dental

Awaiting response at earliest.....

Thanks
Dwarak.K

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 00:56:09

[code]SELECT User FROM Table GROUP BY User HAVING SUM(CASE WHEN Product <> 'Dental' THEN 1 ELSE 0 END) = 0[/code]
EDIT: Removed unwanted where clause
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-12 : 01:12:35

quote:
Originally posted by visakh16

SELECT User FROM Table ~~WHERE Product='Dental'~~ GROUP BY User HAVING SUM(CASE WHEN Product <> 'Dental' THEN 1 ELSE 0 END) = 0

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

Logicaly you do not need Where Clause.
Or

SELECT User
  FROM Table E1
 GROUP BY USER 
HAVING NOT EXISTS 
       (SELECT *
          FROM Table E2
         WHERE Product <> 'Dental'
           AND E1.USER = E2.USER);

dwarakk
Starting Member

3 Posts

Posted - 2010-02-12 : 02:31:30

Hi Visakh

Actually your query works fine for dental,medical,medical and dental when each user has one product for ex(if A has two medical,and one dental A should come as result but as per your query it comes none.

the sample table looks like this
UserName Product
A Medical
A Dental
B Dental
A Medical
B LTD
C LTD

Result:
Medical,Dental - A
Dental,LTD - B
LTD - C

Thanks
Dwarak.K

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-12 : 03:18:34

Try this query. maybe right

   SELECT UserName,
       ( SELECT DISTINCT  Product + ',' 
           FROM table_name T1
          WHERE T1.UserName = T2.UserName          
            FOR XML PATH('') ) AS Products
  FROM table_name T2
 GROUP BY UserName;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources