June 12, 2015 at 9:50 am
Hi, I have 2 tables ... order table (shipping city and destination city) and a referential city table:
1:New York
2:Chicago
3:Atlanta
etc...
In the order table the shipping address and destination address have the identity's a values, I want to write a join to show the names of the cities instead. I'm doing something like this, is there a way to do it in one select?
;with srcemkt as
(
select ID, OrderNumber, b.MarketCenterCity as 'SourceMKT',
from Orders as a
join MarketCity as b
on a.SrceMktID = b.MarketCityId
)
, DestMKT as
(
select
ID, OrderNumber, b.MarketCenterCity as 'DestMKT'
from Orders as a
join MarketCity as b
on a.DestMktID = b.MarketCityId
)
select a.ID, a.OrderNumber, a.SourceMKT, b.DestMKT
from srceMKT as a
join DestMKT on a.OrderNumber = b.OrderNumber
Thanks in advance for your help,
June 12, 2015 at 10:06 am
Please post table scripts and some sample data, as well as expected output.
See here for posting guidelines and tools to help you: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
-- Gianluca Sartori
June 12, 2015 at 10:13 am
Try this SQL. If you are sure that there always be a city info then replace LEFT with INNER.
selectord.ID,ord.OrderNumber,src.MarketCenterCity as 'SourceMKT', dst.MarketCenterCity as 'DestMkt'
fromOrders ord
LEFT JOIN marketCity src on ord.SrceMktId = src.marketCityId
LEFT JOIN marketCity dst on ord.SrceMktId = dst.marketCityId
June 12, 2015 at 10:17 am
You simply have to join to the table twice with different aliases, which is essentially what you were doing by creating two CTEs.
select o.ID, o.OrderNumber, s.MarketCenterCity AS 'SourceMKT', d.MarketCenterCity AS 'DestMKT'
FROM Orders AS o
INNER JOIN MarketCity AS s
ON o.SrceMktID = s.MarketCityId
INNER JOIN MarketCity AS d
ON o.DestMktID = d.MarketCityId
PS: You should ALWAYS use aliases that are mnemonic. When you have very complicated queries, it's very hard to remember which alias refers to which table when you use sequential aliases rather than mnemonic aliases. I tend to use an acronym based on the table name or use. So here I use "o" for orders, "s" for the source market, and "d" for the destination market.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 12, 2015 at 10:58 am
My apologies spaghettidba ... should know better ...
June 12, 2015 at 11:00 am
Thank you amurjani and drew ... so simple ... once I saw it!
June 12, 2015 at 12:36 pm
Or use in-line subqueries:
SELECT ID, OrderNumber,
(SELECT MarketCenterCity
FROM MarketCity
WHERE MarketCityId = o.SrceMktId) AS SourceMKT,
(SELECT MarketCenterCity
FROM MarketCity
WHERE MarketCityId = o.DestMktId) AS DestMKT
FROM Orders o
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply