March 22, 2005 at 9:47 am
Hi guys
Is there any way to do a conditional join in sql.Let me explain my problem with an example
I have two databases EMPTPG and CTJobs
Tables in the EMPTPG database
Employees
Configure
Tables in the CTJobs database
Jobs
EMPTPG database has a table called configure which tells me whether CTJobs database exists or not
what my problem is
i am writing the following query
CREATE PROCEDURE spEmployee
as
DECLARE @errorcode int
SELECT @errorcode = @@Error
IF @errorcode = 0
BEGIN
DECLARE @JobsDB
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[Configure]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
SET @JobsDB = 1
ELSE
SET @JobsDB = 0
SELECT Employees.Empid,CASE
WHEN @JobsDB=1 THEN Jobs.JobName
ELSE NULL
END AS JOBNAME
FROM
EMPLOYEES INNER JOIN CTJobs.DBO.Jobs AS Jobs
WHERE Jobs.JobId>0
END
Go
What i want is i would like to Join the Jobs table in the CTJobs database only when @JobsDB=1
Is there is any way to use the CASE statement in the FROM clause..
Thanks for any help
March 22, 2005 at 10:00 am
this is more a flow job than sql job...
if I got your requirements right I would do this :
if @JobsDB = 1
begin
select * from Employees inner join CTJobs.DBO.Jobs Jobs on ...
end
else
begin
Select * from Employees...
end
Actually I would even create 2 more procs (1 for each statement) and call them accordingly. This allows sql server to cache the most optimized plan for either statement so you always get the most performance. You could also use WITH RECOMPILE in a single proc but that has a little overhead involved.
March 22, 2005 at 10:17 am
Thanks for the reply Remi Gregoire
The solution you recommended is absolutely right if i have to retrieve data from one or two tables
But unfortunately i have to join several table in that query to build my result set.
It is only one of the table i need to check for whether this table existed on the machine in that perticular database or not.
Hope you got my point
Thanks
March 22, 2005 at 11:03 am
I just moved the exists to eliminate the @JobsDB variable
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'Configure' AND XType = 'U')
begin
select * from Employees inner join CTJobs.DBO.Jobs Jobs on ...
end
else
begin
Select * from Employees...
end
The short answer to this problem is this : you can't write a query to uses an object that doesn't exists as it will always error out when the object is not there. If you don't want to use dynamic sql (and I wouldn't recommand it it this case), then you're stuck to use multiple statements. If you have multiple joins, you can create a view that selects all the fields from all the tables that you need to join and work with that view in all your statements. This makes the sql less redundant and easier to maintain. I've built something like this for the printing part of our billing software. I have 1 triage sp that calls 5 others sps (the joins were different on all instances) depending on what the users want. I never had any problems with this setup and I would recommand the same to you.
March 23, 2005 at 3:54 am
Hi Remi Gregoire,
Thanks for the Query you have written for me
I like the idea you have suggested me to create a view that selects all the fields from all the tables that you need to join.
Can you please Give me the sample how can write query like that
Suppose if i have 3 other tables to join like this
SELECT Employees.Empid,CASE
WHEN @JobsDB=1 THEN Jobs.JobName
ELSE NULL
END AS JOBNAME,Table1.Name,Table2.Name,Table3.Name
FROM EMPLOYEES INNER JOIN Table1 ON
Employees.EmpId=Table1.EmpId= INNER JOIN Table2 ON
Employees.EmpId=Table2.EmpId LEFT JOIN Table3 ON
Employees.EmpIdTable3.EmpId INNER JOIN CTJobs.DBO.Jobs AS Jobs
WHERE Jobs.JobId>0
March 23, 2005 at 6:21 am
Hi,
you might simply create the missing table and leave it empty...
(if its joined with an outer join)
karl
Best regards
karl
March 23, 2005 at 6:51 am
hmm.. karl that was not possible.
the table i am joining is actually existed in a different database.Depends on my condition i should use that table..As Remi Gregoire has already suggested to use different select statements .Is there any other way to do this apart from creating seperate select statement.
March 23, 2005 at 7:06 am
That's gonna sound harsh : but if you don't wanna code, don't be a coder. There's no shortcut in this situation so stop looking for one. You'd be long done by now if you'd just had coded the damn thing.
Here's an exemple of a join I have to make in a bunch of SPS, so I just created a view and I use that view to make my selects :
create view vwa
as
SELECT TTR.*, BT.*, T.*, C.*
FROM dbo._Temps_tech_repas TTR INNER JOIN
dbo.[Bon de travail] BT ON TTR.[N° Bon de travail] = BT.[N° Bon de travail] INNER JOIN
dbo.Techniciens T ON TTR.Technicien1 = T.Technicien AND TTR.Technicien1 = T.Technicien INNER JOIN
dbo.CLIENT C ON BT.Société = C.[no client]
GO
then in my SP it's a short clean select.
Select ClientName, PkBT, StartTime from dbo.vwa
March 23, 2005 at 7:31 am
Remi Gregoire i am not against your tip as i am already using that one.
As john had given me a solution to create an empty table ,i had to make your point to him.
If i don't have many tables in my sp to check whether the table exists or not i will definetly go for multiple select statment.
You gave me another idea on how to go with the views and this solution is definetly up for grab as i have situations to do like that..
once again thanks for all the help
March 23, 2005 at 7:36 am
HTH.
Good luck.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply