CODEWITHSUNDEEP
sqlsql-server
nav
nav

Reputation: 57

trying to get a new column with yes,no value

I have two tables:

  1. shortlisted folders with columns (folderid,foldername,userid)
  2. shortlisted users with column(folderid, shortlisteduserid) they are linked with folderid

i am using following query to get list of folders created by userid 50 and count that 50 has shortlisted how many users in each folder

select a.folderid,a.foldername,count(b.ShortlistedUserId) as [count]
from tblshortlistfolders a left outer join tblshortlistedusers b on a.folderid=b.folderid 
where a.userid = 50
group by a.folderid,a.foldername

now i want to add one more column to this result whose values for each row will be as follow

  1. if the folderid contains 49 in tblshortlistedusers userid then value = 'yes'
  2. if the folderid do not contains 49 in tblshortlistedusers userid then value = 'no'

but when i somehow apply this condition , the column count do not show correct value and the result do not get grouped by folders. multiple rows are shown in result for same folder.

how to get the required table result (folderid, foldername, totalcount, 'newcolumn')

Upvotes: 0

Views: 2820

Answers (2)

My Other Me
My Other Me

Reputation: 5117

Would help if you showed your query that you tried. I think this might do what you want: (from memory)

select a.folderid,a.foldername,count(b.ShortlistedUserId) as [count]
,case when count(case when b.ShortlistedUserId=49 then 1 else null end)>0 then 'Yes' else 'No' end as [NewColumn]
from tblshortlistfolders a left outer join tblshortlistedusers b on a.folderid=b.folderid 
where a.userid = 50
group by a.folderid,a.foldername

EDIT: Maybe I am misunderstanding your data. Here is a full example with some sample data:

use tinker -- or whatever test db you have.

CREATE TABLE tblshortlistfolders (
 folderid     INT NOT NULL primary key
,foldername   NVARCHAR(255)
,userid       INT NOT NULL
);

CREATE TABLE tblshortlistedusers (
 folderid            INT NOT NULL
,shortlisteduserid   INT NOT null
,constraint fk_tblshortlistedusers_folderid foreign key (folderid) references [dbo].[tblshortlistfolders]
);

INSERT INTO tblshortlistfolders VALUES ( 100 , 'one'   ,  1); -- created by user 1
INSERT INTO tblshortlistfolders VALUES ( 200 , 'two'   , 50); -- created by user 50
INSERT INTO tblshortlistfolders VALUES ( 300 , 'three' , 50); -- created by user 50
INSERT INTO tblshortlistfolders VALUES ( 400 , 'four'  , 49); -- created by user 49

INSERT INTO tblshortlistedusers VALUES ( 100,  1); -- shortlisted by 50
INSERT INTO tblshortlistedusers VALUES ( 100, 50); -- shortlisted by 01 too
INSERT INTO tblshortlistedusers VALUES ( 200, 50); -- shortlisted by 50
INSERT INTO tblshortlistedusers VALUES ( 200, 49); -- shortlisted by 49 too
INSERT INTO tblshortlistedusers VALUES ( 300, 50); -- shortlisted by 50
INSERT INTO tblshortlistedusers VALUES ( 300, 50); -- shortlisted by  1 too

SELECT a.folderid,a.foldername,COUNT(b.ShortlistedUserId) AS [COUNT]
,CASE WHEN COUNT(CASE WHEN b.ShortlistedUserId=49 THEN 1 ELSE NULL END)>0 THEN 'Yes' ELSE 'No' END AS [NewColumn]
FROM tblshortlistfolders a LEFT OUTER JOIN tblshortlistedusers b ON a.folderid=b.folderid 
WHERE a.userid = 50
GROUP BY a.folderid,a.foldername

This does what I understood form your question. maybe you can give an example of what you want?

Upvotes: 1

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56717

select 
    a.folderid,
    a.foldername,
    count(b.ShortlistedUserId) as [count],
    case when b.userid = 49 then 'yes' else 'no' end as [newcolumn]
from 
    tblshortlistfolders a 
    left outer join tblshortlistedusers b on a.folderid=b.folderid 
where a.userid = 50
group by a.folderid,a.foldername

Upvotes: 0

Related Questions