Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Left Join problem

Author  Topic 

tmarko
Starting Member

15 Posts

Posted - 2008-01-21 : 19:01:04
select CUR.CUR_id,TIM.TIM_id,
CASE WHEN
CUH_averageHisto IS NULL THEN
CUR.CUR_average
ELSE
CUH_averageHisto
END
from TIM_TP TIM
left join CUH_CH CUH on TIM.TIM_id=CUH.CUH_TIM_id
,CUR_C CUR
where CUR.CUR_id=CUH.CUH_CUR_id
order by CUR.CUR_id,TIM.TIM_id

I would like to return all TIM_id from TP not only where CUH_CH got values, Where I lack values I will place values from Cur_C table (Case When), So I thought of using left join to return all values from Tim_TP. But this query only return values where values are found in CUH_CH table.

How should I solve this?

Thanks in advance

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-21 : 19:22:08
where CUR.CUR_id=CUH.CUH_CUR_id or CUH.CUH_CUR_id is null

CODO ERGO SUM
Go to Top of Page

tmarko
Starting Member

15 Posts

Posted - 2008-01-21 : 19:45:55
Thanks, MVJ, so evident when I see it :)
Go to Top of Page

tmarko
Starting Member

15 Posts

Posted - 2008-01-21 : 20:06:30
But not all TIM.TIM_id is supplied after all, I get some missing intervals, strange, need to look into this after all
Go to Top of Page

tmarko
Starting Member

15 Posts

Posted - 2008-01-22 : 04:29:34
Seems like the statement CUH.CUH_CUR_id is null, does NOT check for unique NULL values for each CUH_CUR_id, but for the whole table CUH_CH. How can I get around that? Each Currency in the CUH_CH must return all TIM_id even if that is NULL
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 04:50:38
Worth a try
SELECT		cur.CUR_id,
tim.TIM_id,
COALESCE(cuh.CUH_averageHisto, cur.CUR_average)
FROM TIM_TP AS tim
LEFT JOIN CUH_CH AS cuh ON cuh.CUH_TIM_id = tim.TIM_id
LEFT JOIN CUR_C AS cur ON cur.CUR_id = cuh.CUH_CUR_id
ORDER BY cur.CUR_id,
tim.TIM_id



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

tmarko
Starting Member

15 Posts

Posted - 2008-01-22 : 05:19:12
Nope, does not return all values. Your select does not return all TIM_id for every CUH_CUR_id

thanks anyway
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 07:09:57
Then I must ask you if your JOINS are correct?
Are you JOINing on the proper columns?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 07:19:09
Follow the instructions in this blog post and you will get proper answer quickly.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

tmarko
Starting Member

15 Posts

Posted - 2008-01-23 : 08:26:32
Hi Peso! your query return the values in the CUH_CH table and one set of all Tim_Tp values
like
:
NULL 1448 NULL
NULL 1449 NULL
2 470 .1200000000
2 471 .1200000000

But
I want all the values from CUH_CH +
fill out all remaining TIM_TP values for every CUH_CUR_id that is found in CUH_CH with the value in CUR_avergae for that CUR_id

like

2 1449 0.11
2 469 0.11
2 470 .1200000000
2 471 .1200000000
3 1449 0.10
3 1449 0.11
3 469 0.11
3 470 0.12


Table CUH_CH looks like

CUH_CUR_id,
CUH_TIM_id,
CUH_averageHisto

ex.
1,400,0.13
1,401,0.14
1,403,0.15
....
1,1500,0.19

2,400,0.19
2,401,0.20

...


Table CUR_Currency
CUR_id,
CUR_average,

ex.
1,0.133
2,0.144
3,0.1455

Table TIM_TP
TIM_id

400
401
402
...
1449
1500

Hope this explains better what I would like to do

Kind regards



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-23 : 08:34:20
Can't you please follow the advise in the blog?
Do you really expect us to the this work for you?

Post back proper code for declaring tables together with insert commands.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-23 : 08:38:27
Or try for yourself by changing order for the JOINS.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

tmarko
Starting Member

15 Posts

Posted - 2008-01-23 : 12:07:03
Ok Peso
--drop table CUR_C
CREATE TABLE CUR_C
(
CUR_id INT NOT NULL,
CUR_average decimal(18,10),
)
INSERT INTO CUR_C (CUR_id,CUR_average) Values(2,0.11)
INSERT INTO CUR_C (CUR_id,CUR_average) Values(3,0.12)
INSERT INTO CUR_C (CUR_id,CUR_average) Values(4,0.13)

--drop table CUH_CH
CREATE TABLE CUH_CH
(
CUH_CUR_id INT NOT NULL,
CUH_TIM_id INT NOT NULL,
CUH_averageHisto decimal(18,10)
)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(2,470,0.111)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(2,471,0.122)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(2,472,0.131)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(3,470,0.111)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(3,471,0.122)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(3,472,0.131)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(4,470,0.111)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(4,471,0.122)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(4,472,0.131)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(4,473,0.111)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(4,474,0.122)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(4,475,0.131)

CREATE TABLE TIM_TP
(
TIM_id INT NOT NULL
)
INSERT INTO TIM_TP (TIM_id) values(1)
INSERT INTO TIM_TP (TIM_id) values(2)
INSERT INTO TIM_TP (TIM_id) values(3)
INSERT INTO TIM_TP (TIM_id) values(4)
...

INSERT INTO TIM_TP (TIM_id) values(1400)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-23 : 12:39:24
quote:
Originally posted by tmarko

Ok Peso
--drop table CUR_C
CREATE TABLE CUR_C
(
CUR_id INT NOT NULL,
CUR_average decimal(18,10),
)
INSERT INTO CUR_C (CUR_id,CUR_average) Values(2,0.11)
INSERT INTO CUR_C (CUR_id,CUR_average) Values(3,0.12)
INSERT INTO CUR_C (CUR_id,CUR_average) Values(4,0.13)

--drop table CUH_CH
CREATE TABLE CUH_CH
(
CUH_CUR_id INT NOT NULL,
CUH_TIM_id INT NOT NULL,
CUH_averageHisto decimal(18,10)
)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(2,470,0.111)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(2,471,0.122)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(2,472,0.131)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(3,470,0.111)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(3,471,0.122)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(3,472,0.131)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(4,470,0.111)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(4,471,0.122)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(4,472,0.131)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(4,473,0.111)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(4,474,0.122)
INSERT INTO CUH_CH (CUH_CUR_id,CUH_TIM_id,CUH_averageHisto) Values(4,475,0.131)

CREATE TABLE TIM_TP
(
TIM_id INT NOT NULL
)
INSERT INTO TIM_TP (TIM_id) values(1)
INSERT INTO TIM_TP (TIM_id) values(2)
INSERT INTO TIM_TP (TIM_id) values(3)
INSERT INTO TIM_TP (TIM_id) values(4)
...

INSERT INTO TIM_TP (TIM_id) values(1400)



Hope this is what you are looking for:-


SELECT cc.CUR_id,tt.TIM_id,
CASE
WHEN tt.TIM_id IS NOT NULL ch.CUH_averageHisto
ELSE cc.CUR_average
END
FROM CUH_CH ch
INNER JOIN CUR_C cc
ON cc.CUR_id=ch.CUH_CUR_id
LEFT OUTER JOIN TIM_TP tt
ON tt.TIM_id=ch.CUH_TIM_id
order by cc.CUR_id,tt.TIM_id
Go to Top of Page

tmarko
Starting Member

15 Posts

Posted - 2008-01-23 : 13:03:33
Hi visakh16!

No I tried that also, but only receiving values that already exists in CUH_CH not adding all the remaining rows from TIM_TP.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-24 : 02:27:28
Great! Now we got the proper sample data.
Where is your expected output BASED ON THE SAMPLE DATA YOU POSTED 01/23/2008 : 12:07:03 ?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

tmarko
Starting Member

15 Posts

Posted - 2008-01-24 : 03:08:12
I finally got help from a collegue

The solution

select TIM.TIM_id
, CUR.CUR_id
, isnull((select CUH_averageHisto from CUH_CH CUH where TIM.TIM_id=CUH.CUH_TIM_id and CUR.CUR_id=CUH.CUH_CUR_id),CUR_average)
from TIM_TP TIM, CUR_C CUR
where CUR.CUR_id in (select CUH_CUR_id from CUH_CH group by CUH_CUR_id)

All 1400 rows for every CUR_id found in table CUH_CH either CUH_averageHisto or CUR_average

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-24 : 04:04:08
Good luck with performance!



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

tmarko
Starting Member

15 Posts

Posted - 2008-01-24 : 04:06:44
Yes, that is an issue, but if that is the case, I might put in a temp table
Go to Top of Page
   

- Advertisement -