Select query to get all non matching records

1

How to get those base master record id's progid, projid and contractid which are not matching.

hierarchy is progid - projid - contractid

want to check in my transaction table if any hierarchy combination is not matching.
transaction table is tab_ccsnetrm

hierarchy tables in sequence from top to bottom are 1.) tab_ccsnetprograms, 2.) tab_ccsnetprojects, 3.) tab_ccsnetcontracts.
can you please tell me, i have an issue within my transaction table non matching hierarchy id's happening randomly once in a while. want to get all rm.rmid which has the hierarchy wrong combination.

select rm.rmid, rm.progid, rm.projid, rm.contractid from TAB_ccsNetRM rm
inner join TAB_ccsNetContracts ctr on (rm.contractid=ctr.contractid)
inner join TAB_ccsNetProjects pj on(rm.projid=pj.ProjID)
inner join TAB_ccsNetPrograms pg on(rm.progid=pg.ProgID)

Thanks a lot for the helpful info.

2

Which table in the query is your transaction table?

Where is the hierarchy id in the query?

Do you have foreign keys set up for the related tables?

3

Hi i am sorry here i put together sample datasets all 4 tables:
Declare @Tab_ccsnetprograms table
(progid int)
insert @Tab_ccsnetprograms
select 1 union all
select 2 union all
select 3

Declare @Tab_ccsnetprojects table (progid int, projid int)
insert @Tab_ccsnetprojects
select 1, 201 union all
select 2, 202 union all
select 3, 203

Declare @Tab_ccsnetcontracts table (progid int, projid int, contractid int)
insert @Tab_ccsnetcontracts
select 1, 201, 1001 union all
select 2, 202, 1002 union all
select 3, 203, 1003

---Above three tables are holding hierarchy pair value / master data only, below table is the transaction table, every record must hold a valid hierarchy id's - 3 id combination. randomly having issues sometimes a transaction records somehow ending up wp with wrong hierarchy id's

----Transaction table below third record has wrong hierarchy combination.

Declare @Tab_ccsnetrm table (rmid, int, progid int, projid int, contractid int)
insert @Tab_ccsnetcontracts
select 40001, 1, 201, 1001 union all
select 40002, 2, 202, 1002 union all
select 40003, 3, 203, 1001

third record in transaction table has wrong combination of hierarchy it should be this way:
40003, 3, 203, 1003 but has 1001.

Now when i run the query it should provide RMID 40003, which has non matching hierarchy combination.

4

You could nip the problem in the but with an Instead of trigger on Tab_ccsnetrm or by forcing all inserts/updates/deletes through stored procedures.

Still curious if these tables have foreign key relationships set up (they should!).

5

No it is not setup, these tables are huge with lots of columns, i will set up the foreign key options. i suspect they are not , since i am noticing this issue.

But is there a way to write up a sql to see which RMiID's has the wrong hierarchy combinations.

Thanks.

6

This will do it:

SELECT *
FROM @Tab_ccsnetrm rm
WHERE rm.contractid <> ALL 
(
	SELECT c.contractid
	FROM @Tab_ccsnetcontracts c
	WHERE c.progid = rm.progid
		AND c.projid = rm.projid
)