Advertisement
Guest User

How to query data when primary key exists twice ...

a guest
Jan 29th, 2013
99
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 1.00 KB | None | 0 0
  1. SELECT user_Id, user_Fullname,
  2.        DesignationList, CompanyList
  3. FROM
  4.     (
  5.       SELECT
  6.            user_Id, user_Fullname, user_Zip ,user_Need,
  7.            STUFF(
  8.                (SELECT ', ' + Designation
  9.                 FROM   UserProfession
  10.                 WHERE  Prof_ID = a.user_Id
  11.                 FOR XML PATH (''))
  12.                 , 1, 1, '')  AS DesignationList,
  13.            STUFF(
  14.                (SELECT DISTINCT ', ' + Company
  15.                 FROM   UserProfession
  16.                 WHERE  Prof_ID = a.user_Id
  17.                 FOR XML PATH (''))
  18.                 , 1, 1, '')  AS CompanyList
  19.       FROM UserBasics AS a
  20.       GROUP BY user_Id, user_Fullname, user_Zip ,user_Need
  21.     ) s
  22. WHERE  (@Zip IS NULL OR user_Zip LIKE '%'+@Zip+'%') AND
  23.        (@Interest IS NULL OR user_Need LIKE '%'+@Interest+'%') AND
  24.        (@Company IS NULL OR CompanyList LIKE '%'+@Company+'%') AND
  25.        (a.user_Id != @CurrentID) AND
  26.        (@Designation IS NULL OR DesignationList LIKE '%'+@Designation+'%')
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement