• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

Missing table in join statement - SQL

Hi,
I would like to know if it is possible to still run a statement when a table is missing in a database. In the code below I am joining TABLE1 with TABLE2, the code is part of a view that will exist on multiple databases, but as TABLE1 will always exist TABLE2 will not always exist, so I need to say if TABLE1 and TABLE2 exist run statement :

SELECT * FROM TABLE1 A
INNER JOIN TABLE2 B
ON A.COLUMN1 = B.COLUMN1 AND A.COLUMN2 = B.COLUMN2

else run

SELECT * FROM TABLE1

The view would obviously be more complex with lots of joins etc, so is there a way of just eliminating the INNER JOIN and B.[NAME] if TABLE2 doesn't exist:

CREATE VIEW V_TEST
AS
SELECT A.*, B.[NAME] FROM TABLE1 A
INNER JOIN TABLE2 B
ON A.COLUMN1 = B.COLUMN1 AND A.COLUMN2 = B.COLUMN2

Thank you
SELECT * FROM TABLE1 A
INNER JOIN TABLE2 B
ON A.COLUMN1 = B.COLUMN1 AND A.COLUMN2 = B.COLUMN2

Open in new window

0
crompnk
Asked:
crompnk
2 Solutions
 
Pratima PharandeCommented:
I suggest You could use a stored procedure instead to achieve this functionality.you can not use IF condition in a view

Create Procedure Proc_name

As
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE  TABLE_NAME = 'TABLE2'))

 BEGIN      

SELECT A.*, B.[NAME] FROM TABLE1 A
INNER JOIN TABLE2 B
ON A.COLUMN1 = B.COLUMN1 AND A.COLUMN2 = B.COLUMN2

END  

Else
Begin
SELECT * FROM TABLE1

End




0
 
crompnkData Management SpecialistAuthor Commented:
Hi, Thanks

How would I use the stored procedure in a select query by passing in a column (variable) from TABLE1:

SELECT *
, EXEC Proc_name COLUMN1
FROM TABLE1
DROP TABLE [dbo].[TABLE1]
GO
CREATE TABLE [dbo].[TABLE1](
	[COLUMN1] [varchar](20) NOT NULL,
)
GO

DROP TABLE [dbo].[TABLE2]
GO
CREATE TABLE [dbo].[TABLE2](
	[COLUMN1] [varchar](20) NOT NULL,
	[NAME] [varchar](10) NOT NULL,
)
GO

drop proc Proc_name
GO

INSERT INTO TABLE1 VALUES('ID1')
INSERT INTO TABLE1 VALUES('ID2')
GO

INSERT INTO TABLE2 VALUES('ID1','READ')
INSERT INTO TABLE2 VALUES('ID2','READ')
GO

Create Procedure Proc_name (@column1 varchar(50))

As
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE  TABLE_NAME = 'TABLE2'))

 BEGIN      

SELECT [NAME] FROM TABLE2 WHERE COLUMN1 = @column1

END  

Else
Begin
SELECT 'No TABLE2'

End


SELECT *
, EXEC Proc_name COLUMN1
FROM TABLE1

Open in new window

0
 
Scott PletcherSenior DBACommented:
>>  so is there a way of just eliminating the INNER JOIN and B.[NAME] if TABLE2 doesn't exist: <<

No, unfortunately there's no easy way of doing that.  You basically have to code for either possibility, then create the view accordingly.

For example:
DECLARE @sql varchar(max)
IF OBJECT_ID('dbo.table2', 'U') IS NULL
BEGIN
    SET @sql = 'CREATE VIEW dbo.viewname ... SELECT T1.* FROM TABLE1 T1 WHERE ...'	
END --IF
ELSE
BEGIN
    SET @sql = 'CREATE VIEW dbo.viewname ... SELECT T1.*, T2.NAME FROM TABLE1 T1 INNER JOIN TABLE T2 ON T2.<> = T1.<> AND ... WHERE ...'	
END --IF

IF OBJECT_ID('dbo.viewname', 'V') IS NOT NULL
    DROP VIEW dbo.viewname

EXEC(@sql)

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now