Conditional Join? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Conditional Join?

Author

Topic

kashyap81
Starting Member

9 Posts

Posted - 2008-05-17 : 09:14:43

I feel like there is a better way to do the following but I cannot figure it out:

declare @INPUT_PARAMETER as int
set @INPUT_PARAMETER = null

declare @INPUT_PARAMETER as int
set @INPUT_PARAMETER = null

select * from TableA
inner join
((select TableB.ID1, (TableB.Number1*TableC.Number2) as calculated
from TableB, TableC
where TableB.ID2 = TableC.ID2
and ((@INPUT_PARAMETER is null) OR (TableB.ID2 = @INPUT_PARAMETER)))
union
(select TableA.ID1, 0 from TableA where
(@INPUT_PARAMETER is null) and TableA.ID1 not in (select TableB.ID1 from TableB)))
as TableX
on TableA.ID1 = TableX.ID1

... basically i need to know how i can make an input parameter that will decide whether i should do an inner join or a left join

Thank you.

Kashyap

DiaTree.com
D.I.P. Pvt Ltd

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-17 : 15:18:00

Didnt get what you're trying to do here. Can you explain your requirement please?

kashyap81
Starting Member

9 Posts

Posted - 2008-05-19 : 09:01:34

basically i am wondering if there is way to do this without building a query at runtime. I have tried the following but it does not work.

ALTER PROCEDURE [dbo].[Proc1]
(
@USELEFT int
)
AS
BEGIN
SET NOCOUNT ON;

SELECT * FROM TABLEA.ID, TABLEB.NAME
IF (@USELEFT = 0)
BEGIN
INNER JOIN
END
ELSE IF (@USELEFT = 1)
BEGIN
LEFT JOIN
END
TABLEB ON TABLEA.ID = TABLEB.ID

END

quote:
Originally posted by visakh16

Didnt get what you're trying to do here. Can you explain your requirement please?

DiaTree.com
D.I.P. Pvt Ltd

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-19 : 10:38:52

Always do a LEFT JOIN. Then, in your condition, if you filter out NULL results, that left join becomes an INNER JOIN.

For example:

create procedure test @useleft bit
as
select ....
from a
left outer join b on ....
where
(@useleft = 1 or b.id is not null)

Logically, that says "if @useleft is 1, then don't worry about NULL values in table B. Otherwise, do NOT allow null values in Table B -- which is the same as if you had done an INNER JOIN."
- Jeff
http://weblogs.sqlteam.com/JeffS

kashyap81
Starting Member

9 Posts

Posted - 2008-05-20 : 07:29:30

Oh.. Excellent!.. that worked and made my query about 15% faster than it originally was.

Thank you jsmith.

quote:
Originally posted by jsmith8858

Always do a LEFT JOIN. Then, in your condition, if you filter out NULL results, that left join becomes an INNER JOIN.

For example:

create procedure test @useleft bit
as
select ....
from a
left outer join b on ....
where
(@useleft = 1 or b.id is not null)

Logically, that says "if @useleft is 1, then don't worry about NULL values in table B. Otherwise, do NOT allow null values in Table B -- which is the same as if you had done an INNER JOIN."
- Jeff
http://weblogs.sqlteam.com/JeffS

DiaTree.com
D.I.P. Pvt Ltd

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources