How to query data when primary key exists twice as foreign key in another table?

Question

I am am trying to query data but it shows the same record twice how to distinct?

Table 1: UserBasics

user_Id , user_Fullname , user_Zip , user_Need
--------------------------------------------------------------------
10    Alberto Cesaro    98001    Sales, Marketing & Public Relations

Table 2: UserProfession

Prof_ID , Company , Designation
----------------------------------
10    Young's   Marketing Manager
10    Young's   Regional Manager

My procedure:

CREATE PROC P @Zip         VARCHAR(20)=NULL,
              @Company     VARCHAR(200)=NULL,
              @Designation VARCHAR(100)=NULL,
              @Interest    VARCHAR(200)=NULL,
              @CurrentID   VARCHAR(200)=NULL
--@JobFunc varchar(200)=NULL
AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      SELECT ub.user_Id,
             ub.user_Fullname,
             up.Designation,
             up.Company
      FROM   UserBasics UB
             INNER JOIN UserProfession up
               ON ub.user_Id = up.Prof_ID
      WHERE  ( @Zip IS NULL
                OR ub.user_Zip LIKE '%' + @Zip + '%' )
             AND ( @Interest IS NULL
                    OR ub.user_Need LIKE '%' + @Interest + '%' )
             AND ( @Company IS NULL
                    OR up.Company LIKE '%' + @Company + '%' )
             AND ( ub.user_Id != @CurrentID )
             AND ( @Designation IS NULL
                    OR up.Designation LIKE '%' + @Designation + '%' )
  END

as above is using a stored procedure just to make condition and variable clear

How to show every user data distinct hopes for your suggestion ?

Thanks !

EDIT:

Out put should be look alike,

10  Alberto Cesaro          Marketing Manager,Regional Manager  Young's

EDIT second time :

i have done with company name but there is some problem if i wanna use filter on user profession table column then how would i relate it ?? my query ,

    SELECT
     user_Id, user_Fullname,
     STUFF(
         (SELECT ', ' + Designation 
          FROM   UserProfession as up
          WHERE  Prof_ID = a.user_Id
          FOR XML PATH (''))
          , 1, 1, '') Designation,
      STUFF(
         (SELECT ', ' + Company 
          FROM   UserProfession
          WHERE  Prof_ID = a.user_Id
          FOR XML PATH (''))
          , 1, 1, '')  AS Company
FROM UserBasics AS a
where  (@Zip is null or a.user_Zip like '%'+@Zip+'%') and
    (@Interest is null or a.user_Need like '%'+@Interest+'%') and
    --  (@JobFunc is null or m.mentor_jobFunction= @JobFunc) and
    (@Company is null or up.Company like '%'+@Company+'%') and
    (a.user_Id != @CurrentID) and
    (@Designation is null or up.Designation like '%'+@Designation+'%')
--where   a.user_Zip like '%90005%'
-- WHERE clause here
GROUP BY user_Id, user_Fullname

-- As i am getting error on Company and Designation in where clause Hopes for last suggestion ???

*Alberto Cesaro* appears twice because there are two rows in the `UserProfession` table for him. What would you want to be displayed? — SWeko, Jan 28 '13 at 09:35
possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Martin Smith, Jan 28 '13 at 09:52
If you will select Designation from second table, the record will appear twice for sure cause it has different values for the same ID. Excluding this column from the query and including DISTINCT will give you single row. — Umesh, Jan 28 '13 at 09:54

score 1 · Accepted Answer · answered Jan 28 '13 at 09:54

1

SELECT
     user_Id, user_Fullname,
     STUFF(
         (SELECT ', ' + Designation
          FROM   UserProfession
          WHERE  Prof_ID = a.user_Id
          FOR XML PATH (''))
          , 1, 1, '')  AS DesignationList
FROM UserBasics AS a
-- WHERE clause here
GROUP BY user_Id, user_Fullname

SQLFiddle Demo

answered Jan 28 '13 at 09:54

John Woo

258,903
69
498
492

if there would be some other user with possibility of having more designation then this code would work then too? – Syed Raza Jan 28 '13 at 10:06
i tried it myself and get success but ther is one problem that i mention above in Second edit – Syed Raza Jan 28 '13 at 11:44
thanks for your suggestion but i did it the same way the problem is when i have to use company name and designation name in where clause ? As i mentioned in my second edit – Syed Raza Jan 29 '13 at 05:51
1

oh i get it, you can't use those fields as they were not projected on the inner query, alternatively you can do this as well, http://pastebin.com/TWFyqY3k – John Woo Jan 29 '13 at 06:07

How to query data when primary key exists twice as foreign key in another table?

1 Answers1