Joins on same table

I'm having two general problems trying to do a JOIN. I have a table with
three fields {Code, Date, Amount}. Code+Date is a unique key. I'm trying
to get a rowset with 1) one row for each unique Code+Date pair, 2) and
with each row containing, {Code, Amount for Date-A and Amount for
Date-B}. Basically, I want to create two temp tables with the Amounts for
a specified Date and then Join them.

The problems are
1) I'm trying to do this in SQL-Server 7 with a single stantment, and
2) If a Code+Date pair doesn't have any Amounts, I'd still like a row
returned with NULLs.

Anybody have any wisdom on this??
ThanksThe following gets me what I want, using Temp tables. I'm just trying to
figure out how to combine the Selects into a single statment.

Thanks

----------------

--temp with each Code
Drop Table #T0;
Select Code
Into #T0
From tblSearch
Order by Code;

--temp with amounts for 1st date
Drop Table #T1;
Select Code, Date, Amount
Into #T1
From tblSearch
Where Date = 20031102
Order by Code, Date;

-- amounts for 2nd date
Drop Table #T2;
Select Code, Date, Amount
Into #T2
From tblSearch
Where Date = 20031103
Order by Code, Date;

--put everything together
Select Distinct #T0.Code, #T1.Date, #T1.Amount 'd1', #T2.Date, #T2.Amount
'd2' from #T0
Left Outer Join #T1
On #T0.Code = #T1.Code
Left Outer Join #T2
On #T0.Code = #T2.Code
Order By #T0.Code|||[posted and mailed, please reply in news]

Chris (chris@.hicom.net) writes:
> I'm having two general problems trying to do a JOIN. I have a table
> with three fields {Code, Date, Amount}. Code+Date is a unique key.
> I'm trying to get a rowset with 1) one row for each unique Code+Date
> pair, 2) and with each row containing, {Code, Amount for Date-A and
> Amount for Date-B}. Basically, I want to create two temp tables with
> the Amounts for a specified Date and then Join them.
> The problems are
> 1) I'm trying to do this in SQL-Server 7 with a single stantment, and
> 2) If a Code+Date pair doesn't have any Amounts, I'd still like a row
> returned with NULLs.

Just rewriting the temp-table thing you had with derived tables
gives:

SELECT DISTINCT #T0.Code, #T1.Date, #T1.Amount 'd1',
#T2.Date, #T2.Amount 'd2'
FROM tblSearch #T0
LEFT JOIN (SELECT Code, Date, Amount
FROM tblSearch
WHERE Date = '20031102') AS #T1
ON #T0.Code = #T1.Code
LEFT JOIN (SELECT Code, Date, Amount
FROM tblSearch
WHERE Date = '20031103') AS #T2
ON #T0.Code = #T2.Code
ORDER BY #T0.Code

But if I understand this correctly, it seems that you could get away with:

SELECT Code = coalesce(a.Code, b.Code), a.Date, d1 = a.Amount,
b.Date, d2 = b.Amount
FROM tblSearch a
FULL JOIN tblSearch b ON a.Code = b.Code
AND a.Date = b.Date
AND a.Date = '20031102'
AND b.Date = '20031103'

All this works on SQL7.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Chris,

You can replace temp tables with derived tables. Alternatively, as
the temp tables are selecting from the same table tblSearch, you can
also re-write the query using CASE. Note I'm using a mssqlserver
non-standard syntax. I just find it easier to read.

"Date=CASE when Date = 20031102 then Date else null end"
instead of
"CASE when Date = 20031102 then Date else null end as Date"

SELECT
Distinct
Code,
Date=CASE when Date = 20031102 then Date else null end,
Amount=CASE when Date = 20031102 then Amount else null end,
Date=CASE when Date = 20031103 then Date else null end,
Amount=CASE when Date = 20031103 then Amount else null end,
FROM tblSearch
ORDER BY code

> --temp with each Code
> Drop Table #T0;
> Select Code
> Into #T0
> From tblSearch
> Order by Code;
> --temp with amounts for 1st date
> Drop Table #T1;
> Select Code, Date, Amount
> Into #T1
> From tblSearch
> Where Date = 20031102
> Order by Code, Date;
> -- amounts for 2nd date
> Drop Table #T2;
> Select Code, Date, Amount
> Into #T2
> From tblSearch
> Where Date = 20031103
> Order by Code, Date;
> --put everything together
> Select Distinct #T0.Code, #T1.Date, #T1.Amount 'd1', #T2.Date, #T2.Amount
> 'd2' from #T0
> Left Outer Join #T1
> On #T0.Code = #T1.Code
> Left Outer Join #T2
> On #T0.Code = #T2.Code
> Order By #T0.Code|||The derived table approach gets me what I want -- one row per Code.

It seems that Coalesce doesn't help reduce the normal number of rows from
the Join.

Thanks very much for looking for a solution.|||The derived tables gets both Amounts into the same row, while the Case
still results in two (Distinct) rows.

I need to get a better SQL reference -- the book I'm using does not cover
derived tables.

Thanks very much.|||"Chris" <chris@.hicom.net> wrote in message news:<2bcNb.32425$G04.6661104@.news4.srv.hcvlny.cv.net>...
> The derived tables gets both Amounts into the same row, while the Case
> still results in two (Distinct) rows.
> I need to get a better SQL reference -- the book I'm using does not cover
> derived tables.
> Thanks very much.

Use GROUP if you want combine them into the same row. DISTINCT only
filters the rows.

SELECT
Code,
Date=max(CASE when Date = 20031102 then Date else null end),
Amount=max(CASE when Date = 20031102 then Amount else null end),
Date=max(CASE when Date = 20031103 then Date else null end),
Amount=max(CASE when Date = 20031103 then Amount else null end)
FROM tblSearch
GROUP BY code
ORDER BY code|||Chris (chris@.hicom.net) writes:
> It seems that Coalesce doesn't help reduce the normal number of rows
> from the Join.

That's right. The coalesce() function takes a list of values as parameters,
and return the first value in the list that is not NULL. Since the second
query included a full join, any of a.code and b.code could be NULL, so be
sure that we had a value here, I used coalesce(a.Code, b.Code).

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Excellent!

-- Thanks|||louis nguyen (louisducnguyen@.hotmail.com) writes:
> SELECT
> Code,
> Date=max(CASE when Date = 20031102 then Date else null end),
> Amount=max(CASE when Date = 20031102 then Amount else null end),
> Date=max(CASE when Date = 20031103 then Date else null end),
> Amount=max(CASE when Date = 20031103 then Amount else null end)
> FROM tblSearch
> GROUP BY code
> ORDER BY code

Note that date literals requires quotes. 20031103 is a number, and
attempt to convert it to datetime results in overflow.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp