Click here to Skip to main content
15,917,473 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
SELECT distinct   TM_User.FullName,TM_Profile.profile, TM_User.Report_To,
    
FROM TM_User

join TM_Profile on TM_Profile.ProfileID=TM_User.ProfileID

WHERE TM_User.Is_Active=1 and TM_User.TM_UserID=13
Posted
Updated 8-Jan-14 18:48pm
v4
Comments
Member 10501509 8-Jan-14 23:59pm    
if i remove TM_user.TM_UserID =13 i will not get correct records
Karthik_Mahalingam 9-Jan-14 0:01am    
it means, it will filter only for userid 13
Member 10501509 9-Jan-14 0:05am    
for single user i will get correct records but all for users only i am getting problem.
Member 10501509 9-Jan-14 0:02am    
yes can you filter for all users

First, read my article[^] on when not to use 'distinct'. Then, try giving us the table structure and some sample data so we can try to help you.

Your code (redundantly) requires the user id to be 13 in ALL your subqueries. So of course you only get data for one user ( I guess that's what the other answer means ). You should post your code so that it doesn't contain code that will obviously break it. And, no, we can't rewrite this without knowing the table structure and having some sample data to test against. Ideally, you'll give us some data and tell us the result you are hoping for, so we can make sense of it all.

I think your other issue is you don't alias your tables, so when you say tm_user in a subquery, it's not clear if you mean the local one, or the external one from the main query. you certainly want to filter each subquery by the user id in the main query, or each result will contain the data of ALL your users.

SQL
SELECT distinct   TM_User.FullName,TM_Profile.profile, TM_User.Report_To,
    stuff(( SELECT distinct ', ' + Reference.lookupvalue FROM Reference
            join Reference_Skills on Reference.LookupID=Reference_Skills.SkillLookupKey
            join TM_UserAttributeValue on  Reference_Skills.SkillLookupValue=TM_UserAttributeValue.AttributeValue
            join TM_User on TM_UserAttributeValue.TM_UserID=TM_User.TM_UserID
            where Reference.lookupkey='Platform' and TM_User.TM_UserID  = u.TM_UserId
            FOR XML PATH('')
        ) ,1,2,'') as platfrom,
    stuff(( SELECT ', ' + Reference_Skills.SkillLookupValue FROM Reference_Skills
            join TM_UserAttributeValue on  Reference_Skills.SkillLookupValue=TM_UserAttributeValue.AttributeValue
            join TM_User on TM_UserAttributeValue.TM_UserID=TM_User.TM_UserID
            where AttributeKeyType ='Skill' and TM_User.TM_UserID = u.TM_UserId
            FOR XML PATH('')
        ) ,1,2,'') as skills,
    STUFF( ( SELECT distinct  ', ' + project_name FROM  project
            JOIN task ON project.project_id = task.project_id
            JOIN timesheet ON timesheet.task_id = task.task_id
            join TM_user on TM_user.TM_UserID=timesheet.user_id
            WHERE project.status = 1 and TM_user.TM_UserID = u.TM_UserId
            FOR XML PATH ('')
       ), 1, 2, '') as project,
    STUFF( ( SELECT distinct ', ' + task_name  FROM  task
            JOIN project ON project.project_id = task.project_id
            JOIN timesheet ON timesheet.task_id = task.task_id
             join TM_user on TM_user.TM_UserID=timesheet.user_id
            WHERE project.status = 1 and TM_user.TM_UserID = u.TM_UserId
            FOR XML PATH ('')
        ), 1, 2, '') as task_name

FROM TM_User u

join TM_Profile on TM_Profile.ProfileID=TM_User.ProfileID

WHERE TM_User.Is_Active=1


is what I'd try, but it's impossible to really help without the tables and data to work with.
 
Share this answer
 
v2
remove this TM_user.TM_UserID =13
 
Share this answer
 
Comments
Karthik_Mahalingam 9-Jan-14 0:10am    
take your code in 3 blocks and run each block by block and try to rectify the problem. it will be the simple method.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900