November 13, 2008 at 1:44 pm
is it possible to do a case statement in a join. I have an order table which needs to join to a location table. It can join on the order_location
or if the shiptolocation has a value it needs to join instead of the order_location.
November 13, 2008 at 2:04 pm
You can't have a CASE in the join statement as that syntax is invalid
I'm assuming you want to join from one table (call this TableA) to different tables (call these TableB and TableC) based on the value of some column in TableA?
In this case LEFT OUTER JOIN to both tables and use COALESCE to get the appropriate value.
Something like:
SELECT TableA.Column,COALESCE(TableB.Column,TableC.Column) FROM TableA LEFT OUTER JOIN TableB ON .... LEFT OUTER JOIN TableC ON ....
More info at http://weblogs.sqlteam.com/jeffs/archive/2007/04/03/Conditional-Joins.aspx
November 14, 2008 at 7:36 am
No actually join to one other table based upon columns in the first table. If shipto is null use location, if shipto has a value use that and ignore location.
November 14, 2008 at 7:53 am
timscronin (11/14/2008)
No actually join to one other table based upon columns in the first table. If shipto is null use location, if shipto has a value use that and ignore location.
I hope I understand you correctly, but making some sample data (like Jeff Moden describes in one of his articles) increases the chance people would react to your post in a way that helps you.
Depends how many columns you want to use, but I would make the query this way:
SELECT ISNULL(T1.shipto, T2.location)
FROM Table1 T1
LEFT OUTER JOIN Table2 T2 ON T1.shipto = T2.shipto
LEFT OUTER JOIN Table2 T3 ON T1.location = T2.location
Other way is to indeed make it an inner join with a case statement, but to me that makes it less readable. To make a query that way, I would really prefer some test data and desired output first.
Ronald
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
November 14, 2008 at 7:59 am
timscronin (11/13/2008)
is it possible to do a case statement in a join. I have an order table which needs to join to a location table. It can join on the order_locationor if the shiptolocation has a value it needs to join instead of the order_location.
Post your query along with the DDL for the tables, some sample data for the tables, and the expected output of the query. If you need help figuring out how to do all that, read the article referenced in my signature line below.
Depending on how you are using the case, it may be possible to use it. I have done it in a few queries, but need more info to see if it is possible here.
November 14, 2008 at 8:06 am
Order table
orderid shipto locationid
1 acf fcd
2 acf
3 dre acf
Location
locationid descr
acf AkronFolley
fcd Fowley
dre Dresley
Idea is to join shipto or if null, locationid
to location table
November 14, 2008 at 8:27 am
timscronin (11/14/2008)
Order tableorderid shipto locationid
1 acf fcd
2 acf
3 dre acf
Location
locationid descr
acf AkronFolley
fcd Fowley
dre Dresley
Idea is to join shipto or if null, locationid
to location table
Have you read that article (http://www.sqlservercentral.com/articles/Best+Practices/61537/)? That shows how to script real fast the sample data, so we can help and test real fast.
The desired output should look like this then?
orderid locationid descr
1 acf AkronFolley
2 acf Fowley
3 dre Dresley
If that's the case the query would generally look like as I wrote before:
SELECT O.OrderID,
ISNULL(L1.LocationID, L2.LocationID) LocationID,
ISNULL(L1.descr, L2.descr) descr
FROM Orders O
LEFT OUTER JOIN Location L1 ON O.shipto = L1.LocationID
LEFT OUTER JOIN Location L2 ON O.LocationID = L2.LocationID
This just follows my way of thinking, maybe better if it isn't yours...
Ronald
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
November 14, 2008 at 8:56 am
thanks this worked, Should have thought about the left outer joins
November 14, 2008 at 8:59 am
I wouldn't use a case statement either. Here is what i would do (and some of this you should have done, ie the create table and insert statements)
create table dbo.Orders (
OrderId int,
ShipTo varchar(3),
LocationId varchar(3)
);
create table dbo.Location (
LocationId varchar(3),
Descr varchar(25)
);
insert into dbo.Orders (OrderId, ShipTo, LocationId)
select 1, 'acf', 'fcd' union all
select 2, null, 'acf' union all
select 3, 'dre', 'acf';
insert into dbo.Location (LocationId, Descr)
select 'acf', 'AkronFolley' union all
select 'fcd', 'Fowley' union all
select 'dre', 'Dresley';
select * from dbo.Orders;
select * from dbo.Location;
select
*
from
dbo.Orders o
inner join dbo.Location l
on (l.LocationId = coalesce(o.ShipTo,o.LocationId));
drop table dbo.Orders;
drop table dbo.Location;
November 14, 2008 at 9:01 am
Or ISNULL:
CREATE TABLE #Orders (orderid INT, shipto VARCHAR(3), locationid VARCHAR(3))
INSERT INTO #Orders (orderid, shipto, locationid)
SELECT 1, 'acf', 'fcd' UNION ALL
SELECT 2, NULL, 'acf' UNION ALL
SELECT 3, 'dre', 'acf'
CREATE TABLE #Location (locationid VARCHAR(3), descr VARCHAR(20))
INSERT INTO #Location (locationid, descr)
SELECT 'acf', 'AkronFolley' UNION ALL
SELECT 'fcd', 'Fowley' UNION ALL
SELECT 'dre', 'Dresley'
SELECT o.*, l.*
FROM #Orders o
LEFT JOIN #Location l ON ISNULL(o.shipto, o.locationid) = l.locationid
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 14, 2008 at 9:16 am
[EDIT] Nevermind, I see I need to drink a gallon of coffee to keep up with this thread. Removed redundant repost of solutions posted while I as typing.
November 14, 2008 at 3:58 pm
Just for S & G's, I added another select statement to my test code. In it you will see that I use a case in the join. If you copy all the code, you will see that it too, works.
--Order table
--
--orderid shipto locationid
--1 acf fcd
--2 acf
--3 dre acf
--
--Location
--
--locationid descr
--acf AkronFolley
--fcd Fowley
--dre Dresley
--
--Idea is to join shipto or if null, locationid
--to location table
--
create table dbo.Orders (
OrderId int,
ShipTo varchar(3),
LocationId varchar(3)
);
create table dbo.Location (
LocationId varchar(3),
Descr varchar(25)
);
insert into dbo.Orders (OrderId, ShipTo, LocationId)
select 1, 'acf', 'fcd' union all
select 2, null, 'acf' union all
select 3, 'dre', 'acf';
insert into dbo.Location (LocationId, Descr)
select 'acf', 'AkronFolley' union all
select 'fcd', 'Fowley' union all
select 'dre', 'Dresley';
select * from dbo.Orders;
select * from dbo.Location;
select
*
from
dbo.Orders o
inner join dbo.Location l
on (l.LocationId = coalesce(o.ShipTo,o.LocationId));
select
*
from
dbo.Orders o
inner join dbo.Location l
on (l.LocationId = case when o.ShipTo is not null then o.ShipTo else o.LocationId end);
SELECT O.OrderId,
ISNULL(L1.LocationId, L2.LocationId) LocationId,
ISNULL(L1.Descr, L2.Descr) descr
FROM dbo.Orders O
LEFT OUTER JOIN dbo.Location L1 ON O.ShipTo = L1.LocationId
LEFT OUTER JOIN dbo.Location L2 ON O.LocationId = L2.LocationId
drop table dbo.Orders;
drop table dbo.Location;
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply