Querying data from 3 different tables based -checking if tables have matching records if not then display them

Question

I have 3 tables that I'll somehow need to join. I've been thinking about creating queries in MS Access but I'd rather stay away from that and hopefully can do it right from SQL SERVER with a single query.

Here are my tables and what I'm trying to accomplish.

Tbl1 - Main table 
NoClient     LocID      
123          23          
133          45          
880          25          
123          55          

tbl2
NoClient     ClinNo      
123          112233      
123          223344      
133          334455      

tbl3
ClinNo       NoClient      IssueNo
112233       123           7788979
223344       123           4564133

There are quite a few bit more columns that are not shown here.

MY GOALS:

Tbl1 is my main table where data gets inserted. I need to be able to display all records where:

• tbl1 has a value with NoClient - but there is no corresponding value in tbl2. This one is tricky because tbl1 might have multiple instances of the same client (NoClient 123). In this case there are 2 records in tbl2 so all good but...If tbl1 has two record but tbl2 has only 1 - then show the record from tbl1 that doesn't have a corresponding record in tbl2.
• if tbl2 has the same number of records tbl1 has for a given client - need to check if there is are corresponding values in tbl3 for that NoClient and ClinNo(since now we have two fields to go by). If there is - need to just check that IssueNo in tbl3 is not Null. If it is null - then display that record.

With the data shown above - my desired result would be:

NoClient     LocID              
133          45          
880          25  

133 would be shown because this record while has a corresponding value in tbl2 - does not have a value in tbl3. 880 would be shown because it does not have a record in tbl2 or tbl3.

Is it possible to do something like that with one query or am i over my head?

Answer 1

Try this:

declare @t table (
    NoClient int not null,
    LocId int not null
)
declare @t1 table (
    NoCLient int not null,
    ClinNo int not null
)
declare @t2 table (
    ClinNo int not null,
    NoClient int not null,
    IssueNo int not null
)

insert into @t (NoClient, LocId) values (123,23),(133,45),(880,25),(123,55)
insert into @t1 (NoCLient, ClinNo) values (123,112233),(123,223344),(133,334455)
insert into @t2 (ClinNo, NoClient, IssueNo) values (112233,123,7788979),(223344,123,4564133)

-----

select * 
from 
    (select NoClient, LocId, RANK() over (partition by NoClient order by NoClient, LocId) as LocNr from @t) as tr1
    left outer join (select NoClient, ClinNo, RANK() over (partition by NoClient order by NoClient, ClinNo) as CliNr from @t1) as tr2 
        on tr1.NoClient = tr2.NoCLient and tr1.LocNr = tr2.CliNr
    left outer join @t2 as tr3 on tr2.NoCLient = tr3.NoClient and tr2.ClinNo = tr3.ClinNo
where tr2.NoCLient is null or tr3.IssueNo is null

This uses Rank() and an over clause to assign a number to each row for the same NoClient in both @t (corresponding to your table tbl1) and @t1 (tbl2). It then left joins these together, then further left joins @t2 (tbl3) and filters out rows where everything is linked up all the way to @t2.

Answer 2

I think you're making things overcomplicated. From what I gather - you simply need all records from table1 that have no corresponding (or null) records in table3 or no corresponding records in table 2. If this is the case, then this relatively simple query should do it:

select * from tbl1 where NoClient not in (select tbl2.NoClient from tbl2)
union all
select * from tbl1 where NoClient not in (select tbl2.NoClient from tbl2       
inner join tbl3 on tbl2.NoClient=tbl3.NoClient and tbl2.ClinNo=tbl3.ClinNo 
where tbl3.IssueNo is not null)