January 27, 2008 at 7:05 pm
Hi,
Is the following output possible.
I want to display two fields from two different tables into one as below.
Table A - field is BerthID, TariffCode, Actual_Unit
Table B - fields are BerthID, Order_No
Table C - Fields are Order_No, TariffCode, Est_Unit
Output like below
TariffCode | Actual_Unit | Est_Unit
-------------------------------------------------
A.TariffCode | 7 | 4
A.TariffCode | 9 | 3
C.TariffCode | 9 | null
C.TariffCode | 11 | 15
C.TariffCode | null | 4
Can any one help me on this.:crying:
January 28, 2008 at 1:28 am
Some examples of the data in tables A, B & C would help in the understanding...
You could do something like
select
coalesce(A.TarrifCode, C.TarrifCode) as TarrifCode,
A.Actual_Unit,
C.Est_Unit
from A
full outer join B
on A.BerthID = B.BerthID
full outer join C
on B.Order_No = C.Order_No
and A.TarrifCode = C.TarrifCode
I'm assuming that you want all rows from A & C in the output. If they match in tarrifCode and through their table B link then you want them to share the same row. Is that correct?
January 28, 2008 at 3:45 am
Hi Thanks for your valuable time.
For the below code i get only the tariff_codes from table A
select
B.Order_No,
coalesce(C.Tariff_Code, A.Tariff_Code) [Tariff Code],
A.Unit [Actual Unit],C.Unit [Est Unit]
from
Coo_Vessel_Tariff A
full outer join Coo_Bforma_Invoice B on A.BerthID = B.BerthID
full outer join Coo_Bforma_Invoice_Detail C on B.Order_No = C.Order_No
and A.Tariff_Code = C.Tariff_Code
where A.BerthID = 1247
and for the below code i get only the tariff_codes from table C
select
B.Order_No,
coalesce(C.Tariff_Code, A.Tariff_Code) [Tariff Code],
A.Unit [Actual Unit],C.Unit [Est Unit]
from
Coo_Vessel_Tariff A
full outer join Coo_Bforma_Invoice B on A.BerthID = B.BerthID
full outer join Coo_Bforma_Invoice_Detail C on B.Order_No = C.Order_No
and (A.Tariff_Code = C.Tariff_Code or A.Tariff_Code != C.Tariff_Code)
where A.BerthID = 1247
January 28, 2008 at 2:02 pm
Here is an example of joining them together.
Just a guess. However, you are doing full outer joins, so appearently there are some nulls involved.
IF OBJECT_ID('tempdb.dbo.#a') IS NOT NULL
DROP TABLE #a
IF OBJECT_ID('tempdb.dbo.#b') IS NOT NULL
DROP TABLE #b
IF OBJECT_ID('tempdb.dbo.#c') IS NOT NULL
DROP TABLE #c
CREATE TABLE #a (BerthID INT,TariffCode INT,Actual_Unit INT)
CREATE TABLE #b (BerthID INT,Order_No INT)
CREATE TABLE #c (Order_No INT,TarriffCode INT,Est_Unit INT)
SELECT [#a].BerthID,
[#a].TariffCode,
[#a].Actual_Unit,
[#b].BerthID,
[#b].Order_No,
[#c].Order_No,
[#c].TarriffCode,
[#c].Est_Unit
FROM [#a]
INNER JOIN [#b]
ON [#a].BerthID = [#b].BerthID
INNER JOIN #c
ON [#c].Order_No = #b.Order_No
AND [#c].TarriffCode = [#a].TariffCode
January 28, 2008 at 2:09 pm
Also you will want to read this
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply