SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Conditional Joins
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

IanKM
Starting Member

United Kingdom
18 Posts

Posted - 09/20/2012 :  12:24:46  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 09/20/2012 :  23:27:00  Show Profile  Reply with Quote
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/

Go to Top of Page

IanKM
Starting Member

United Kingdom
18 Posts

Posted - 09/21/2012 :  05:00:29  Show Profile  Reply with Quote
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
Go to Top of Page

Andy Hyslop
Starting Member

United Kingdom
14 Posts

Posted - 09/21/2012 :  06:11:39  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 09/21/2012 :  13:04:33  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000