?
Solved

Missing table in join statement - SQL

Posted on 2011-10-20
3
Medium Priority
?
312 Views
Last Modified: 2012-05-12
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
Comment
Question by:crompnk
3 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 1000 total points
ID: 36999106
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
 

Author Comment

by:crompnk
ID: 37000969
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
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 37003001
>>  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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question