SQLTeam.com | Weblogs | Forums

Help with SQL query

#1

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

#2

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
#3 
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