Hey Troops,
I'm not sure if this is allowed, but I'm trying to keep my SP generic and reduce duplicate code. Depending on a condition, I need to JOIN to different tables, which slightly changes the result set. So, I would include conditions later to grab the proper fields based on checking the same condition (@TravelType = 'A').
Here's a sample of what I'm doing, which is currently giving me a syntax error near 'CASE':
CASE WHEN @TravelType = 'A'
THEN
LEFT JOIN AirportCity DepCity
ON DepFrom = DepCity.Code
LEFT JOIN AirportCity ArrCity
ON ArrTo = ArrCity.Code
ELSE
LEFT JOIN City DepCity
ON DepartureAirportCode = DepCity.CityCode
AND DepCity.TypeCode = 'R'
LEFT JOIN City ArrCity
ON ArrivalAirportCode = ArrCity.CityCode
AND ArrCity.TypeCode = 'R'
END
Can someone shed some light on why this throws an error? Or, is there another way to accomplish such?
Thanks!
12 51406
Try something like this... -
-
FROM YourMainTable
-
LEFT JOIN AirportCity DepCity ON @TravelType = 'A' and DepFrom = DepCity.Code
-
LEFT JOIN AirportCity DepCity ON @TravelType = 'B' and SomeOtherColumn = SomeOtherColumnFromSomeOtherTable
-
-
Happy Coding!!!
~~ CK
Is that putting the condition within the ON clause? So, it will only do the first JOIN if @TravelType = 'A' and only do the second JOIN if @TravelType = 'B'?
I see other examples like that, but the @TravelType in our case would need to be a field in the table being joined to. @TravelType is just a local variable. But if you're saying it'll work, I'll give it a shot.
It actually does both joins but only one of the joins will return any records because @TravelType will be either 'A' or it will be 'B'
Nice approach CK
you may need a case in your select clause so it will select the appropriate field based on the value of @TravelType
Ok, so you can put a condition in the 'ON' clause... not just the columns to link on? Slick. We actually found another way to get what we need, but I did learn something. Thanks much for your time fellas.
Yes you can.
You can even do an always true condition like ON 1 = 1 to pair each record on your main table to every single record on your joined table. This is specially helpful if you need a sequence of some sort for every record in the main table.
So make sure about your conditions...
Happy Coding!!!
~~ CK
@stretchtack
I have also got a similar issue. Need to have a conditional JOIN. I tried with CASE statements but I got syntax error near CASE.
Eg:
IF @Type=A, then I need to JOIN with tableA and get TableA.Name
IF @Type=B, then I need to JOIN with tableB and get TableB.Name
IF @Type=C, then I need to JOIN with tableC and get TableC.Name
Can someone help me out in this.
Post the code that is giving you the error
Here is the code..
CREATE PROCEDURE GetDetails
@input1 AS VarCHAR(50) = '',
@inputGuid AS UniqueIdentifier
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
SELECT [FinalTable].[Name1] AS 'Name1', [FinalTable].[Name2] AS 'Name2',
FROM MainTable
CASE [MainTable].[Type]
WHEN 'A' THEN
INNER JOIN [TableA] [FinalTable] ON [MainTable].[Id] = [FinalTable].[Id]
WHEN 'B' THEN
INNER JOIN [TableB] [FinalTable] ON [MainTable].[Id] = [FinalTable].[Id]
END
WHERE [MainTable].[Id1] = 1
END TRY
BEGIN CATCH
--Rethrow the error.
END CATCH
SET NOCOUNT OFF
END
Based on the type (A or B), the JOIN has to be formed. If the type is 'A' then JOIN should be only with TableA and not with TableB.Similarly if type is 'B' then should be only with TableB.
The columns, "Name1" land "Name2" should be selected from either of these two tables based on the type.
Can you explain what [FinalTable] is.
I am asssuming that you mean it as an alias
If that is the case then
this seems more appropriate but I am only guessing at what you are trying to do -
SELECT
-
CASE WHEN [MainTable].[Type]='A' THEN
-
[TableA].[Name1] ELSE [TableB].[Name1]
-
END
-
AS 'Name1',
-
CASE WHEN [MainTable].[Type]='A' THEN
-
[TableA].[Name2] ELSE [TableB].[Name2]
-
END
-
AS 'Name2',
-
FROM MainTable
-
LEFT JOIN [TableA] ON [MainTable].[Id] = [FinalTable].[Id]
-
LEFT JOIN [TableB] ON [MainTable].[Id] = [FinalTable].[Id]
-
-
Incidentally you cannot do conditional joins like that.
You cannot include the table to join in the true/false parts of the case.
The two tables to join must be outside the case altogether and only the joining fields can be inside.
I hope that makes sense -
SELECT *
-
FROM theTable a
-
JOIN theOtherTable b on a.field =
-
CASE WHEN Condition=True THEN
-
b.TrueJoinField ELSE b.FalseJoinField
-
END
-
You can include other conditions in your JOIN, not just relational. Try something like this: -
-
-
-
SELECT
-
Name =
-
case
-
when MainTable.Type = 'A' then [FinalTableA].[Name1]
-
when MainTable.Type = 'B' then [FinalTableB].[Name1]
-
else NULL
-
end
-
FROM MainTable
-
left join [TableA] [FinalTableA] ON [MainTable].[Type] = 'A' and [MainTable].[Id] = [FinalTableA].[Id]
-
left join [TableA] [FinalTableB] ON [MainTable].[Type] = 'B' and [MainTable].[Id] = [FinalTableB].[Id]
-
WHERE [MainTable].[Id1] = 1
-
-
Happy Coding!!!
~~ CK
Sign in to post your reply or Sign up for a free account.
Similar topics
by: sdowney717 |
last post by:
Select LOCGeneralHave.LSCH, LOCSubClassHave.LSCH from LOCGeneralHave ,
LOCSubClassHave Where (LOCGeneralHave.LCNT <> '0' and
LOCSubClassHave.LCNT = '0')
This query seems to be ignoring the...
|
by: Marizel |
last post by:
I'm not sure there's an easy solution to this, but thought I'd ask. I
often find myself with a query which I'd like to reuse, but with a
different datasource. These datasources generally have...
|
by: Dennis Gearon |
last post by:
I am working on a design where a location for something can have:
Fully qualified address with even building names, room numbers, and
booth numbers.
**-OR-**
GPS location
**-OR-**
Both
...
|
by: Coquette |
last post by:
Hi all. I'm new here. I wanted to ask your opinion about *joining two
tables* from *two different database* together. Is *that* possible? My
project requires me to join identical tables from...
|
by: Reader |
last post by:
Hello all,
I am joining two tables in a query and the output should be all those
records where two fields in table1 match two corresponding fields in
table2.
I joined the tables using both...
|
by: EyesFriedOpen |
last post by:
I was trying to post this to the IBM DB2 forum, however I do not have the "New Post" button available when I view the IBM DB2 forum. Therefore, I apologize if this is not the right place for this.
...
|
by: regikutty |
last post by:
im a begginer,im using sql server 2005
i have 3 different tables
Customer table
intcid
Companyname
ProjectTable
|
by: Cyprus106 |
last post by:
Apparently, Im incapable of properly executing this query! I've been at this for far too long and gotten nowhere. Forgive me if this is a umb mistake; I'm not great at SQL.
I've got three tables,...
|
by: bscott |
last post by:
I'm sure there is a simple solution to this, but being fairly new to Access I
would appreciate some assistance...
I have a query that joins two tables. One table has call details for each day...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |