Conditional Joins - 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)

Conditional Joins

Author

Topic

IanKM
Starting Member

18 Posts

Posted - 2012-09-20 : 12:24:46

Hi there

I would like to know if what im trying is possible
In the software that i do support for we have customer memos. What actual memos that customer has is held on a table called 'Customer memos'

Memos can have various different datatypes Text,Date,Money these are all held on one table called customer memos.
There is also one other type we call a 'due date', which is a group of 3 dates (this is used to see when a customer last renewed the subs and when their due again)

Table 1 'Customer Memos'
Memoid/CustId/TextValue/DateValue/MoneyValue
Table 2 'Customer Dues'
memoid/Custid/lastdue/lastActual/nextdue

For the Client (My Customer) we have set something that allows them to choose what cust memo they want to report on. which would be fine if all the memos are all on one table. I think i need to be able to say
Case Memo Id = "1" then
Join this table
else
Join this table
END

or something like that
This Is what i have already


Declare @MEMO
SET @memo = {{User Picks What Memo they want}}
SELECT c.CustId, c.displayname,
CASE 
WHEN @Memo = 22 THEN cd.NextdueDate
WHEN @Memo = 88 THEN cm.DateValue
END
AS Memo
FROM Cust c
INNER JOIN custdues cd ON c.custid = cd.custid AND cd.memoid = 22
INNER JOIN custmemos cm ON c.custid = cm.custid

But no matter that the user pick the code thinks that its looking in both tables for the one field
Can i do this in one report or will i need two

Ian

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-20 : 23:27:00

you cant conditional join to a table. what you can do instead is this


Declare @MEMO
SET @memo = {{User Picks What Memo they want}}
SELECT c.CustId, c.displayname,
r.DateVal AS Memo
FROM Cust c
CROSS APPLY
(SELECT Nextduedate AS DateVal
 FROM custdues
 WHERE custid = c.custid 
 AND @Memo=22

 UNION ALL

 SELECT DateValue
 FROM custmemos
 WHERE custid = c.custid 
 AND @Memo=88
)r

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

IanKM
Starting Member

18 Posts

Posted - 2012-09-21 : 05:00:29

Hi visakh16

Many thanks for your responce i must say Cross Apply looks a bit complicated and after reading about it im not sure that i understand it enough to use.

Is it possible to do something like this


Declare @MEMO
SET @memo = {{User Picks What Memo they want}}

Case 
WHEN Memo = 22 then 
**Do A select statement**
WHEN Memo = 88 Then
**Do A Different Select Statement**

Sadly Im not an SQL expert but to be able to do this would seem logical

Andy Hyslop
Starting Member

14 Posts

Posted - 2012-09-21 : 06:11:39

Hi

You can use and IF ELSE block to achieve this:


DECLARE @TEST INT

SET @TEST = 1

IF @TEST = 1

SELECT
'1 SELECTED'

ELSE IF @TEST = 0

SELECT 
'SOME OTHER VALUE'

If you change the @TEST variable to something onther than 1 it will run the 2nd statement

Andy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-21 : 13:04:33

quote:
Originally posted by IanKM

Hi visakh16

Many thanks for your responce i must say Cross Apply looks a bit complicated and after reading about it im not sure that i understand it enough to use.

Is it possible to do something like this

Declare @MEMO
SET @memo = {{User Picks What Memo they want}}

Case
WHEN Memo = 22 then
**Do A select statement**
WHEN Memo = 88 Then
**Do A Different Select Statement**

Sadly Im not an SQL expert but to be able to do this would seem logical

can you explain the issue faced?

see below to understand apply operator

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources