Master records with no transactional data

Transact-SQL
1

Hope I'm posting in the right section. I have a master table which contains a unique ID for each company. The company ID is used in 7 transactional tables where I need to isolate all instances where we have a company record with no transactional data.

I run a query like the one below for each company and copy the results to Excel where I combine and filter the results as needed. I was wondering if it is possible to rewrite the script to run against all 7 tables at once and use the distinct statement to return only unique values.

select [ID], [Base] from [Company] left outer join [Transact1] on ([Company].[ID] = [Transact1].[Vendor])
where [Transact1].[Vendor] is null

2

Try with UNION or UNION ALL (depending of your needs)

    select [ID], [Base] from [Company] left outer join [Transact1] on ([Company].[ID] = [Transact1].[Vendor])
    where [Transact1].[Vendor] is null
    UNION
    select [ID], [Base] from [Company] left outer join [Transact2] on ([Company].[ID] = [Transact2].[Vendor])
    where [Transact2].[Vendor] is null
    UNION
    select [ID], [Base] from [Company] left outer join [Transact3] on ([Company].[ID] = [Transact3].[Vendor]) 
    where [Transact3].[Vendor] is null
    select [ID], [Base] from [Company] left outer join [Transact4] on ([Company].[ID] = [Transact4].[Vendor])
    where [Transact4].[Vendor] is null
    UNION
    select [ID], [Base] from [Company] left outer join [Transact5] on ([Company].[ID] = [Transact5].[Vendor])
    where [Transact5].[Vendor] is null
    UNION
    select [ID], [Base] from [Company] left outer join [Transact6] on ([Company].[ID] = [Transact6].[Vendor]) 
    where [Transact6].[Vendor] is null
1 Like
3

sure its possible. combine the 7 tables using your select and the UNION ALL statement. Wrap the results as a subquery (or CTE). The main query would take distinct results from the subquery.

1 Like
4

Sorry for the late reply. I was able to use the UNION statement to get the company ID's from each table. My ultimate goal was to flag only the companies that were inactive in all seven tables. Is that possible through SQL? I already saved the output to Excel and used a formula to get the results I needed but was wondering for future reference.

Thanks again for the help!

5

This perhaps?

select [ID], [Base] 
from [Company] 
WHERE NOT EXISTS
      (
          SELECT * 
          FROM [Transact1] 
          WHERE [Company].[ID] = [Transact1].[Vendor]
      )
      AND NOT EXISTS
      (
          SELECT * 
          FROM [Transact2] 
          WHERE [Company].[ID] = [Transact2].[Vendor]
      )
... repeat for Trasnsact3-to-7 ...