SQL joining multiple tables

Question

Using Microsoft SQL 2000, I would like to join tables (A, B, C, and D) together. I know that table A always exists. However, I only know that at least one of the tabular forms exists (B, C, D).

Is there a way to do something like this to accomplish what I'm trying to do?

Select * form table a If table b exists left Join table b on a.id = b.id If table c exists left Join table c on a.id = c.id If table d exists left Join table d on a.id = d.id 

Answer 1

You will need to view data dictionary views for this and use dynamic SQL

 declare @myquery varchar(1000) set @myquery = 'Select * from a ' if exists (select * from sysobjects where xtype='U' and name = 'b') begin set @myquery = @myquery + 'inner join b on b.id = a.id ' end if exists (select * from sysobjects where xtype='U' and name = 'c') begin set @myquery = @myquery + 'inner join c on c.id = a.id ' end if exists (select * from sysobjects where xtype='U' and name = 'd') begin set @myquery = @myquery + 'inner join d on d.id = a.id ' end exec( @myquery) 

I used sysobjects , however you are advised to use " Information Schema Views " instead

AND GREAT FAILURE FROM DYNAMIC SQL

<strong> Benefits

• It provides flexibility and scalability.
• It can reduce the number of lines of code written.

disadvantages

• It can become very complicated and difficult to read. Think about quotes embedded in quotation marks and other similar things.
• This can adversely affect code stability. Some dynamic SQL errors will not be known until runtime. (For example, here you are referencing a nonexistent table)
• Dynamic SQL is harder to test than equivalent static SQL. It may also be impossible to verify all the possible circumstances that your Dynamic SQL will encounter, thereby introducing its inherent risk.
• It will be harder to conduct effective impact analysis on Dynamic SQL in your codebase.
• SQL injection and misuse. Dynamic SQL is more prone to misuse and invariably less secure than static SQL
• Query code in dynamic SQL is not subject to the query plan and, as such optimizations may be skipped. This way it can be slower than equivalent static SQL
• Because the SQL query is not known before execution, it may be more difficult to configure SQL Dynamic code (for example, determine the indexes that may be required in a table).

Answer 2

The following is a request. * should never be part of a query, so it’s better to mention column names.

 declare @query varchar(1000) set @query = 'Select ColumnName from a ' if exists (select Object_ID from sys.tables where name = 'b') begin set @query = @query + 'inner join b on b.id = a.id' end if exists (select Object_ID from sys.tables where name = 'c') begin set @query = @query + 'inner join c on b.id = c.id' end if exists (select Object_ID from sys.tables where name = 'd') begin set @query = @query + 'inner join d on d.id = a.id' end exec( @query) 

Answer 3

You cannot make a conditional join this way.

You can just do a regular LEFT JOIN. If no rows match the join criteria, these columns will be NULL:

 Select * from table a left Join table b on a.id = b.id left Join table c on a.id = c.id left Join table d on a.id = d.id 

Columns b. * may be NULL or c columns. * may be NULL or columns d. * may be NULL.

If you need to select the first non-NULL column, use COALESCE:

 Select *, COALESCE(b.SOMECOLUMN, c.SOMECOLUMN, d.SOMECOLUMN) AS SOMECOLUMN from table a left Join table b on a.id = b.id left Join table c on a.id = c.id left Join table d on a.id = d.id 

As commentators note, if the tables do not exist, this will not work. I think that I would really advocate for the promotion and creation of tables so that your schemes always meet expectations. Dynamic SQL is a pain to maintain and debug, and static SQL and schemas can be queried to ensure that they meet expectations using metadata (i.e. a procedure or view will not be valid if the table is missing, and dependencies can be viewed explicitly )

Answer 4

I assume that you mean if the results are not tables themselves.

 SELECT * FROM TABLEA OUTER JOIN TABLEB ON TABLEA.id = TABLEB.id OUTER JOIN TABLEC ON TABLEA.id = TABLEC.id OUTER JOIN TABLED ON TABLEA.id = TABLED.id 

you just get zeros for columns where the value did not match.

so you can filter with

 WHERE TABLEB.id is not null 

etc.