What are the keys? Without keys, any solution for such problems can be
unreliable; in any case try:

SELECT DISTINCT u1.UserId, f1.FacilityID, f1.FacilityName
FROM Facility f1
CROSS JOIN UserLink u1
WHERE f1.FacilityID NOT IN (SELECT 1 UNION
SELECT u1.FacilityID
FROM UserLink u1) ;

--- or

SELECT (SELECT MAX(UserID) FROM UserLink) AS "user_id",
f1.FacilityID, f1.FacilityName
FROM Facility f1
WHERE f1.FacilityID <> 1
AND NOT EXISTS (SELECT *
FROM UserLink u1
WHERE u1.FacilityID = f1.FacilityID) ;

--
- Anith
( Please reply to newsgroups only )

Try:

select
31 as UserID
, f.*
from
Facility as f
where
f.FacilityID <> 1
and not exists
(
select
*
from
UserLink as u
where
u.FacilityID = f.FacilityID
and u.UserID = 31
)

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql


[quoted text, click to view]
Consider the following DDLs:

CREATE TABLE Facility (FacilityID int,FacilityName varchar(50))
INSERT INTO Facility VALUES (1,'Entire Org')
INSERT INTO Facility VALUES (101,'Central Lab')
INSERT INTO Facility VALUES (102,'District Complex')
INSERT INTO Facility VALUES (103,'District Junior College')
INSERT INTO Facility VALUES (104,'District Senior College')
INSERT INTO Facility VALUES (105,'District High School')
INSERT INTO Facility VALUES (106,'School Elementary')
INSERT INTO Facility VALUES (107,'Dept. Of Education')
INSERT INTO Facility VALUES (108,'District Junior College')
INSERT INTO Facility VALUES (109,'Colam Sessions Court')
INSERT INTO Facility VALUES (110,'Sports Association')
INSERT INTO Facility VALUES (111,'Film Auditorium')
INSERT INTO Facility VALUES (112,'State Judge')
----------------------------------------------------------------------------
---------------------------------
CREATE TABLE UserLink (UserID int,FacilityID int)
INSERT INTO UserLink VALUES (31,101)
INSERT INTO UserLink VALUES (31,104)
INSERT INTO UserLink VALUES (31,111)
----------------------------------------------------------------------------
--------------------------------------

Now I want a recordset using the above 2 DB tables which should have only
those FacilityIDs & FacilityNames which have not been selected by UserID=31
i.e. the recordset should have records other than FacilityID=101, 104 & 111.
Also FacilityID=1 should be neglected. The recordset should look something
like this:

UserID-------FacilityID-----FacilityName
31-----------102---------District Complex
31-----------103---------District Junior College
31-----------105---------District High School
31-----------106---------School Elementary
31-----------107---------Dept. Of Education
31-----------108---------District Junior College
31-----------109---------Colam Sessions Court
31-----------110---------Sports Association
31-----------112---------State Judge

How do I frame the SQL query?

Thanks,

Consider the following DDLs:

CREATE TABLE Facility (FacilityID int,FacilityName varchar(50))
INSERT INTO Facility VALUES (1,'Entire Org')
INSERT INTO Facility VALUES (101,'Central Lab')
INSERT INTO Facility VALUES (102,'District Complex')
INSERT INTO Facility VALUES (103,'District Junior College')
INSERT INTO Facility VALUES (104,'District Senior College')
INSERT INTO Facility VALUES (105,'District High School')
INSERT INTO Facility VALUES (106,'School Elementary')
INSERT INTO Facility VALUES (107,'Dept. Of Education')
INSERT INTO Facility VALUES (108,'District Junior College')
INSERT INTO Facility VALUES (109,'Colam Sessions Court')
INSERT INTO Facility VALUES (110,'Sports Association')
INSERT INTO Facility VALUES (111,'Film Auditorium')
INSERT INTO Facility VALUES (112,'State Judge')
----------------------------------------------------------------------------
---------------------------------
CREATE TABLE UserLink (UserID int,FacilityID int)
INSERT INTO UserLink VALUES (31,101)
INSERT INTO UserLink VALUES (31,104)
INSERT INTO UserLink VALUES (31,111)
----------------------------------------------------------------------------
--------------------------------------

Now I want a recordset using the above 2 DB tables which should have only
those FacilityIDs & FacilityNames which have not been selected by UserID=31
i.e. the recordset should have records other than FacilityID=101, 104 & 111.
Also FacilityID=1 should be neglected. The recordset should look something
like this:

UserID-------FacilityID-----FacilityName
31-----------102---------District Complex
31-----------103---------District Junior College
31-----------105---------District High School
31-----------106---------School Elementary
31-----------107---------Dept. Of Education
31-----------108---------District Junior College
31-----------109---------Colam Sessions Court
31-----------110---------Sports Association
31-----------112---------State Judge

How do I frame the SQL query?

Thanks,

Arpan