Condition in innerjoin - 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 2005 Forums

Transact-SQL (2005)

Condition in innerjoin

Author

Topic

muthukumar.csm
Starting Member

18 Posts

Posted - 2008-12-29 : 01:08:57

hi everybody,
i have 3 innerjoin in my query i need to execute the 3rd innerjoin in some condition.can anyone help me

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-29 : 01:10:52

Could you post an example to make it more clear? Joins can accept conditions in the ON clause.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 01:11:23

what do you mean by execute inner join in some condition? can you elaborate with some data sample?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-29 : 01:11:40

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

muthukumar.csm
Starting Member

18 Posts

Posted - 2008-12-29 : 22:56:17

select * from tblA innerjoin tblB ON tblA.X = tblB.X innerjoin tblC ON tblA.X = tblC.Y
i need to execute the 2nd innerjoin on some condition for example if i am passing some parameter.i need to innerjoin with tblC when that Parameter is not null.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 00:29:18

did you mean this?


select * from tblA 
innerjoin tblB 
ON tblA.X = tblB.X 
innerjoin tblC 
ON tblA.X = tblC.Y
OR @Param IS NULL

muthukumar.csm
Starting Member

18 Posts

Posted - 2008-12-30 : 01:25:33

ALTER PROCEDURE [dbo].[sp_Rpt_TotalSaleByQtr]
@UserID varchar(50)
,@Merchant nvarchar(100)
,@MerchantSub nvarchar(100)-- if this is Null or 0 then search by Merchant

AS
BEGIN
SET NOCOUNT ON;
IF @MerchantSub = '0' SET @Branch_No = ''
SELECT [YearID] ,RIGHT([YearQtrID],1) [QtrID]
FROM [dbo].[FactTransByYear]
INNER JOIN dbo.DIMBRANCH
ON [FactTransByYear].[MERCHANT] = [DIMBRANCH].[MERCHANT]
******
INNER JOIN dbo.[DIMMERCHANTSUBGROUP]
ON [DIMMERCHANTSUBGROUP].[MERCHANT]=[DIMMERCHANT].[MERCHANT]
*********
In this case if Merchantsub is '' the above innerjoin(******) condition wouln't execute

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 01:32:16

[code]
ALTER PROCEDURE [dbo].[sp_Rpt_TotalSaleByQtr]
@UserID varchar(50)
,@Merchant nvarchar(100)
,@MerchantSub nvarchar(100)-- if this is Null or 0 then search by Merchant

AS
BEGIN
SET NOCOUNT ON;
IF @MerchantSub = '0' SET @Branch_No = ''
SELECT [YearID] ,RIGHT([YearQtrID],1) [QtrID]
FROM [dbo].[FactTransByYear]
INNER JOIN dbo.DIMBRANCH
ON [FactTransByYear].[MERCHANT] = [DIMBRANCH].[MERCHANT]
INNER JOIN dbo.[DIMMERCHANTSUBGROUP]
ON [DIMMERCHANTSUBGROUP].[MERCHANT]=[DIMMERCHANT].[MERCHANT]
OR NULLIF(@MerchantSub,'0') IS NULL
[/code]

muthukumar.csm
Starting Member

18 Posts

Posted - 2008-12-30 : 02:04:20

i am getting diff o/p when commanding(--) the 2nd innerjoin and not commanding that.I THING ITS NOT CHECKING THE CONDITION.

Thiyagu_04
Starting Member

37 Posts

Posted - 2008-12-30 : 02:20:17

ALTER PROCEDURE [dbo].[sp_Rpt_TotalSaleByQtr]
@UserID varchar(50)
,@Merchant nvarchar(100)
,@MerchantSub nvarchar(100)-- if this is Null or 0 then search by Merchant

AS
BEGIN
SET NOCOUNT ON;
Declare @SQL Varchar(8000)
IF @MerchantSub = '0' SET @Branch_No = ''
SET @SQL=' SELECT [YearID] ,RIGHT([YearQtrID],1) [QtrID]
FROM [dbo].[FactTransByYear]
INNER JOIN dbo.DIMBRANCH
ON [FactTransByYear].[MERCHANT] = [DIMBRANCH].[MERCHANT] '
--******
IF @MerchantSub <> '' AND @MerchantSub IS NOT NULL
SET @SQL= @SQL + 'INNER JOIN dbo.[DIMMERCHANTSUBGROUP]
ON [DIMMERCHANTSUBGROUP].[MERCHANT]=[DIMMERCHANT].[MERCHANT]'
--*********
EXEC (@SQL)

try this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 03:55:47

quote:
Originally posted by muthukumar.csm

i am getting diff o/p when commanding(--) the 2nd innerjoin and not commanding that.I THING ITS NOT CHECKING THE CONDITION.

you mean when you pass @MerchantSub as ''

muthukumar.csm
Starting Member

18 Posts

Posted - 2008-12-30 : 03:56:51

its good idea.but i have long select statement as well as where & having conditions after innerjoins .so i am not able to put exec(@SQL) in the middle .anyway i try. if any other solution u have tell me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 04:00:49

what should be condition under which you want to ignore second inner join?

muthukumar.csm
Starting Member

18 Posts

Posted - 2008-12-30 : 04:34:09

i am passing MerchantSub null or '0' and i have long select statement and 2 innerjoin then long where condition then group by etc...
check this
INNERJOIN tblB ON tblA.X = tblB.X
INNERJOIN tblC ON tblA.X = tblC.X
INNERJOIN tblD ON tblD.X = tblC.X
INNERJOIN tblE ON tblE.X = tblC.X

if the Merchnatsub is null the any one of above innerjoin must not execute.
please give me the solution.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 04:40:28

[code]INNERJOIN tblB ON tblA.X = tblB.X OR NULLIF(@MerchantSub,'0') IS NULL
INNERJOIN tblC ON tblA.X = tblC.X OR NULLIF(@MerchantSub,'0') IS NULL
INNERJOIN tblD ON tblD.X = tblC.X OR NULLIF(@MerchantSub,'0') IS NULL
INNERJOIN tblE ON tblE.X = tblC.X OR NULLIF(@MerchantSub,'0') IS NULL[/code]

muthukumar.csm
Starting Member

18 Posts

Posted - 2008-12-30 : 05:07:25

OR NULLIF(@MerchantSub,'0') IS NULL

can u please explain this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 05:12:51

quote:
Originally posted by muthukumar.csm

OR NULLIF(@MerchantSub,'0') IS NULL

can u please explain this

it checks if @MerchantSub is '0' and converts it to NULL. this will cause both 0 as well NULL to equate to true and then it wont look for other condition in join (tblA.X = tblB.X,tblA.X = tblC.X,...). So whenever you pass 0 or NULL the inner join wont be considered at all.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-30 : 05:12:58

NULLIF returns the first expression if the two expressions are not equivalent. If the expressions are equivalent, NULLIF returns a null value of the type of the first expression

muthukumar.csm
Starting Member

18 Posts

Posted - 2008-12-30 : 05:16:32

ACTUALLY NULLIF RETURNS A NULL VALUE IF TWO EXPRESSION IS EQUAL.CONSIDER THAT I HAVE ONLY ONE CONDITION IE @MerchantSub is '0'.now u tell me the solution

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 05:18:34

[code]INNERJOIN tblB ON tblA.X = tblB.X OR @MerchantSub='0'
INNERJOIN tblC ON tblA.X = tblC.X OR @MerchantSub='0'
INNERJOIN tblD ON tblD.X = tblC.X OR @MerchantSub='0'
INNERJOIN tblE ON tblE.X = tblC.X OR @MerchantSub='0'[/code]

muthukumar.csm
Starting Member

18 Posts

Posted - 2008-12-30 : 05:23:58

i thing i have some problem in this SP.i check with other and get back u tomorrow.thanks 4 ur response.bye

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources