SQLTeam.com | Weblogs | Forums

Help with SQL query

I need help writing a query

I have 2 table with below structure
Table : RMmaster
RMID | RMname|

Table : Relationship
ID | RelationshipName | RM1 | RM2 | RM3 |

(RM1, RM2, RM3 map to RMID values from RMmaster table.)

I want to write a query to show all the records from RMmaster table which do not exist in RM1 or RM2 or RM3 fields of the Relationship table.

Thanks for your help

ED

Please provide sample data as DDL and DML as follows. This is a sample you will need to provide your own for the question you are asking. Help us help you

--the following is DDL Data Definition Language
create table #jeevad(Documenttype varchar(50),
 Filepath varchar(50),Remarks varchar(50), Filename varchar(50),
DocumentNo int, Hijri  nvarchar(50),Gregorian nvarchar(50), issuedate date)

--the following is DML Data Manipulation Language
insert into #jeevad
select 'CR Certificate SAB General Contracting.', 
'c:/filename test', NULL, 123, NULL, NULL, NULL union

--etc etc etc
SELECT RM.*
FROM RMmaster RM
LEFT OUTER JOIN (
    SELECT DISTINCT ca1.*
    FROM Relationship
    CROSS APPLY (
        VALUES(ID, RM1),(ID,RM2),(ID,RM3)
    ) AS ca1(ID,RMID)
    WHERE RMID <> ''
) AS REL  ON  REL.RMID = RM.RMID
WHERE REL.RMID IS NULL