Query multiple tables using one join

user_10314773 0
Viewed 124

I need to query multiple tables that contain the column name 'idClient' for a specific condition.

So far I acquire the tables that I need to query using the following query:

SELECT c.name  AS 'ColumnName',
       t.name AS 'TableName'
FROM        sys.columns c (nolock)
JOIN        sys.tables  t   (nolock) ON c.object_id = t.object_id
WHERE       c.name LIKE '%idClient%'
ORDER BY    TableName,
            ColumnName;

This gives me the following result (in reality there are roughly 100 tables returned): 

+------------+-----------------+
| ColumnName |    TableName    |
+------------+-----------------+
| idClient   | tbClient        |
| idClient   | tbClientContact |
| idClient   | tbInvoice       |
+------------+-----------------+

In order for me to find all client records in each of the tables I am currently running 3 separate queries for each table name. For example: 

SELECT * FROM tbClientContact (nolock)
    JOIN tbClient (nolock)
        ON tbClientContact.idClient = tbClient.idClient
WHERE tbClient.vcSurname = 'Smith'

Instead of running the above query 3 times for each table, is there an easier way to run the same query on all results that are returned as TableName?

GOAL: I have been tasked with - in the example above - removing any client records from a database where the client surname is 'Smith'. I am running the above SELECT query to find whether the idClient of all clients with the surname of 'Smith' will leave orphan records in tables where there is a link of 'idClient'. I am joining tbClient as the column vcSurname does not exist in any other table than tbClient.

sql-server sql
4 Answers

You could try

SELECT idClient FROM tbClient WHERE vcSurname = 'Smith'
UNION ALL
SELECT idClient FROM tbClientContact WHERE vcSurname = 'Smith'
UNION ALL
SELECT idClient FROM tbInvoice  WHERE vcSurname = 'Smith'

If you need more columns output, all three queries must have the same number of ouput columns and all of the same type

Edit As others have suggested, it would be helpful to know what you are doing as the method you are attempting is never the best way of doing something. However, the cursor solution below should build a dynamic query to do what you want

DECLARE @table AS NVARCHAR(128)
DECLARE @sql AS NVARCHAR(4000)

DECLARE c CURSOR FOR
    -- hey all the tables with the columns LIKE '%idClient%'
    SELECT t.name AS 'TableName'
    FROM        sys.columns c (nolock)
    JOIN        sys.tables  t   (nolock) ON c.object_id = t.object_id
    WHERE       c.name LIKE '%idClient%'
    ORDER BY    TableName


-- loop through the results of the query line by line
OPEN c
FETCH NEXT FROM c INTO @table

WHILE @@FETCH_STATUS = 0
BEGIN

    -- build the query dynamically
    SET @sql =CONCAT(@sql,'SELECT idClient FROM ' + @table +  ' WHERE vcSurname = ''Smith'' UNION ALL ')
    FETCH NEXT FROM c INTO @table
END

-- remove last "UNION ALL" text
SET @sql = STUFF(@sql,LEN(@sql)-9,11,'')
EXEC sp_executesql @stmt = @sql

CLOSE c
DEALLOCATE c

EDIT EDIT Just seen your edit. Does your table have foreign / primary key pairs, do the foreign keys have ON DELETE CASCADE? If so, it should just be a case of 

DELETE from tbClient WHERE vcSurname = 'Smith'
user_70319000

For automated query:

After question Edit:

SELECT 'SELECT '+c.name+' FROM '+t.name+' T WITH(nolock)
    JOIN tbClient (NOLOCK)
        ON T.idClient = tbClient.idClient
WHERE vcSurname = ''Smith''
        UNION ALL
        '
FROM        sys.columns c (nolock)
JOIN        sys.tables  t   (nolock) ON c.object_id = t.object_id
WHERE       c.name LIKE '%idClient%'

PRINT the result in text format (ctrl+T). Copy the complete result and then remove last UNION ALL

user_18410540

1 Determine involved tables by querying systables (sysforeignkeys or syscolumns if you don't have FKs).

2 Manually write a comprehensive stored proc that is meant to do everything right 

create proc dbo.Client_Del
  @client_id int
as
begin try
  if not exists(select 1 from dbo.Client c where c.id = @client_id)
    raiserror("Client %d not found", 16, 1, @client_id)

  begin tran

  delete ct
  from dbo.ClientContacts ct
  where ct.client_id = @client_id

  delete idt
  from dbo.InvoiceDetail idt
  inner join dbo.Invoice i
     on i.invoice_id = idt.invoice_id
  where i.client_id = @client_id

  delete i
  from dbo.Invoice i
  where i.client_id = @client_id

  delete c
  from dbo.Client c
  where c.client_id = @client_id

  commit tran
end try
begin catch
  if @@trancount > 0
     rollback tran
  throw
end catch
GO

3 Invoke your stored proc with an argument

declare @id int

set @id = (select c.client_id from dbo.Client c where c.LastName = 'Smith')

exec dbo.Client_Del
  @client_id = @id
user_59218260

I am not sure on what is your need. And this post is enhanced code of @Luv. Use of this, you just run it.

declare @select nvarchar(max) = N''
set @select = 
N'
declare @sql nvarchar(max) = N''''
SELECT @sql += '' UNION ALL SELECT ''+c.name+'' FROM ''+t.name+'' T WITH(nolock)
    JOIN tbClient (NOLOCK)
        ON T.idClient = tbClient.idClient
WHERE vcSurname = ''''Smith''''''
FROM        sys.columns c (nolock)
JOIN        sys.tables  t   (nolock) ON c.object_id = t.object_id
WHERE       c.name LIKE ''%yourColumnName%'' 
set @sql = STUFF(@sql, 1, 10, '''')
print @sql
exec sp_executesql @sql
'
exec sp_executesql @select
user_96456460
Related