CODEWITHSUNDEEP
sqlselect
Dharamraj Patel
Dharamraj Patel

Reputation: 15

How do I fetch some specific records from SQL database table

I have database table name(temp_vendor) as under 

material nvarchar(255)
vendor  nvarchar(255)

and data in table are like as under

-cbxsasp000034 , Supack
-cbxsasp000034 , FAIRPAC
-CBXSASP000043 , VISHAL 
-HDBGPLN000062, MAHALAXMI
-cbxsasp000034 , VIM PAC
-cbxsasp000034 , Supack
-cbxsasp000034 , Supack
-CBXSASP000043 , Supack
-CBXSASP000043 , Supack
-6000004,     RAMA ploycon

my problem is that I want select only which record whose material and vendor is only 1. means from table which material's supplier is only one. how do I query for that? please help me, Thanks in Advance please

Upvotes: 2

Views: 49

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

if you need all the column selection you can try by using exists

select t1.* from temp_vendor t1
where exists ( select 1 from temp_vendor t2 where t1.material=t2.material
                                            having count(distinct vendor)=1)

Upvotes: 0

Mureinik
Mureinik

Reputation: 311428

You can group by the material and count the number of vendors:

SELECT   material
FROM     temp_vendor
GROUP BY material
HAVING   COUNT(DISTINCT vendor) = 1

Upvotes: 2

Related Questions