CODEWITHSUNDEEP
sqlsql-serverif-statementinner-join
francops henri
francops henri

Reputation: 517

Inner join with if statement

I am trying to build the following sp :

USE [MarMoniApp]
GO
/****** Object:  StoredProcedure [dbo].[Desc_by_date]    Script Date: 10/23/2012     09:01:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Desc_by_date]
@DateToSearch varchar(50)
AS
BEGIN
select a.ticker,a.First_T, a.Last_T, a.Size_mln, a.pct_d_vol,a.a_trad,a.coolname,a.recency,b.Analyst
from Order_Desc a

CASE
When a.coolname = 'ELTORO'
then (Inner join MarMoniApp.dbo.Namelist b on a.ticker = b.ticker )
END

where convert(datetime, cast(rtrim(First_T) AS nvarchar)) > DateToSearch 
order by  CONVERT(DATETIME, cast(rtrim(First_T) AS nvarchar) )  DESC
END

I want to do the inner join only if the "coolname" is equal to eltoro, but it seems that my syntax is not correct.

Upvotes: 1

Views: 12285

Answers (4)

Sachin Jain
Sachin Jain

Reputation: 31

You should use Left Outer Join as below:

select a.ticker,a.First_T, a.Last_T, a.Size_mln, a.pct_d_vol,a.a_trad,a.coolname,a.recency,b.Analyst
from Order_Desc a
LEFT OUTER JOIN MarMoniApp.dbo.Namelist b
on a.ticker = b.ticker
**where 
(   
    (a.coolname <> 'ELTORO')
    OR
    (a.coolname = 'ELTORO' AND a.ticker = b.ticker)
)** 
AND convert(datetime, cast(rtrim(First_T) AS nvarchar)) > DateToSearch 
order by  CONVERT(DATETIME, cast(rtrim(First_T) AS nvarchar) )  DESC

Upvotes: 2

MortenSickel
MortenSickel

Reputation: 2200

Disclaimer: I am working mostly with postgres and mysql so I don't know the CASE-syntax... But, the way I read your SQL, I would have written the part from the "CASE" as

WHERE 
Inner join MarMoniApp.dbo.Namelist b on a.ticker = b.ticker and a.coolname='ELTORO')
and convert(datetime, cast(rtrim(First_T) AS nvarchar)) > DateToSearch 
order by  CONVERT(DATETIME, cast(rtrim(First_T) AS nvarchar) )  DESC

(but of cource, I may have misunderstood the idea of the CASE-syntax)

Upvotes: 0

Grzegorz Gierlik
Grzegorz Gierlik

Reputation: 11232

Probably you need something like:

INNER JOIN
 MarMoniApp.dbo.Namelist b 
ON 
 a.coolname = 'ELTORO'
 AND a.ticker = b.ticker

Basically -- you cannot do conditional JOIN. You can only select rows to JOIN.

If you need columns from MarMoniApp.dbo.Namelist table only if a.coolname = 'ELTORO' use LEFT JOIN instead.

Another option (i.e. in case of performance is the key) is to use UNION ALL as @Turcia suggested (use NULL as value of extra column for part without JOIN).

Upvotes: 0

Turcia
Turcia

Reputation: 711

What about making two select statement and then union them.

select ... from Order_Desc a
Inner join MarMoniApp.dbo.Namelist b on a.ticker = b.ticker
where a.coolname = 'ELTORO' AND ...

union

select ... from Order_Desc a
where a.coolname != 'ELTORO' AND ...

Upvotes: 2

Related Questions