TSQL, Select Value only if it belongs to one Primary key Otherwise return null

Using TSQL, I have a table that holds filenames of Pictures for products. Different products can be using the same picture. I need to select the filenames for a single product only if it does not exists for a different product.

I have tried Where Exists (select FileName From Tbl where Prod_Id = @var) AND NOT EXISTS(select FileName From Tbl where Prod_Id != @var) In the Select Statement. Can it be done, If So What am I Missing 

July 18th, 2015 1:40am

Hi 77Beast.

Try this:

CREATE TABLE #Tbl
(
	Prod_Id int
	, FileNamePict varchar(50)	
)
insert into #Tbl (Prod_id, FileNamePict) values (1,'a'),(2,'b'),(3,'c'),(4,'a'),(5,'a'),(6,'c'),(7,'g'),(8,'h')

declare @var int
set @var = 2
;with CTE as
(
	select Prod_Id, FileNamePict, count(FileNamePict) over(partition by FileNamePict) as Nr from #Tbl 
)
select FileNamePict from CTE where Nr = 1 and Prod_Id = @var 
drop table #Tbl

Correcting your code, instead:

select FileNamePict From #Tbl where Prod_Id = @var and not exists(select 1 from #tbl t where Prod_Id != @var and t.FileNamePict = #tbl.FileNamePict)


Free Windows Admin Tool Kit Click here and download it now
July 18th, 2015 2:58am

Thanks A lot, I tried comparing filename earlier but did not think about using an alias on table. Thank you again , this works just as I need it. So glad their is people smarter then me willing to help.
July 18th, 2015 3:14am

When you help another in the same way then you will be considered more smart.
So, sharing knowledge enriched selves and each other.
Good luck.
Free Windows Admin Tool Kit Click here and download it now
July 18th, 2015 3:28am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics