Similar Records Remove One

Question

Well, i posted a similar question earlier, and got a solution for it, but i am doing something similar and little different, and i need a little help.

There is a Seeker table, employer table, category table ,interest table..

FK_is_id = Seeker ID
FK_ie_id = Employer ID
FK_ic_id = Category ID

Interest Table Data:

int_id   FK_is_ID  FK_ie_ID  FK_ic_ID
1           2          null      3
2           2          null      5
3           null       1         3
4           null       3         1
5           null       2         5
6           4          null      1

What i want to do is, filter out, employers (FK_ie_ID) who have same interest as the logged in employer (eid), Something like Rivals or Competition.

With the following Query, i am able to do what i want, the problem is, its also retriving the logged in user in the result.... I only employers other than the logged in user..

SELECT ins.int_id, ins.FK_is_ID, ine.FK_ie_ID,ins.FK_ie_ID,emp.e_name,ine.int_id
FROM Interests ins
INNER JOIN Interests ine ON ins.FK_ic_ID = ine.FK_ic_ID
FULL OUTER JOIN employers emp ON emp.e_id=ine.FK_ie_id
WHERE ins.FK_ie_ID = @eid
  AND ine.FK_ie_ID IS NOT null

Which database server are you using? And which version? "SQL" is not the name of a database server. — John Saunders, Dec 30 '14 at 05:11
That's "SQL **Server** Express 2010". Again, "SQL" is not the name of a database product. — John Saunders, Dec 30 '14 at 05:41
So do you have any idea about what i can do about this problem? — Rifshan, Dec 30 '14 at 05:47

score 0 · Answer 1 · answered Dec 30 '14 at 09:58

I think the following will accomplish your result, can you verify:

DECLARE @eid INT
SELECT @eid = 3

DECLARE @Interest TABLE 
(
 int_id INT NOT NULL,
 FK_is_ID INT NULL,
 FK_ie_ID INT NULL,
 FK_ic_ID INT NULL
)

DECLARE @Employers TABLE 
(
 e_id INT NOT NULL,
 e_name VARCHAR(MAX) NULL
)

INSERT INTO @Interest
SELECT * FROM
(
 SELECT 1 AS int_id, 2  AS FK_is_ID, NULL AS FK_ie_ID, 3 AS FK_ic_ID
 UNION ALL
 SELECT 2 AS int_id, 2  AS FK_is_ID, NULL AS FK_ie_ID, 5 AS FK_ic_ID
 UNION ALL
 SELECT 3 AS int_id, NULL AS FK_is_ID, 1  AS FK_ie_ID, 3 AS FK_ic_ID
 UNION ALL
 SELECT 4 AS int_id, NULL AS FK_is_ID, 3  AS FK_ie_ID, 1 AS FK_ic_ID
 UNION ALL
 SELECT 5 AS int_id, NULL AS FK_is_ID, 2  AS FK_ie_ID, 5 AS FK_ic_ID
 UNION ALL
 SELECT 6 AS int_id, 4  AS FK_is_ID, NULL AS FK_ie_ID, 1 AS FK_ic_ID
) AS DATA

INSERT INTO @Employers
SELECT * FROM
(
 SELECT 1 AS e_id, 'one' AS e_name
 UNION ALL
 SELECT 2 AS e_id, 'two' AS e_name
 UNION ALL
 SELECT 3 AS e_id, 'three' AS e_name 
) AS DATA


SELECT 
 Interest.int_id, 
 Interest.FK_is_ID,
 Employers.e_name
FROM 
 @Interest AS Interest
LEFT OUTER JOIN
 @Employers AS Employers
 ON Interest.FK_ie_id = Employers.e_id 
INNER JOIN
 (
  SELECT
   InterestSub.FK_ic_ID
  FROM 
   @Interest AS InterestSub
  WHERE
   InterestSub.FK_ie_ID = @eid
 ) AS SubData
 ON Interest.FK_ic_ID = SubData.FK_ic_ID
WHERE
 COALESCE(Interest.FK_ie_ID, '') <> @eid

Similar Records Remove One

1 Answers1