| Author |
 Topic  |
|
|
tmarko
Starting Member
15 Posts |
 Posted - 01/21/2008 : 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)
USA
6997 Posts |
Posted - 01/21/2008 : 19:22:08
|
where CUR.CUR_id=CUH.CUH_CUR_id or CUH.CUH_CUR_id is null
CODO ERGO SUM |
 |
|
|
tmarko
Starting Member
15 Posts |
Posted - 01/21/2008 : 19:45:55
|
| Thanks, MVJ, so evident when I see it :) |
|
|
|
tmarko
Starting Member
15 Posts |
Posted - 01/21/2008 : 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 |
Edited by - tmarko on 01/22/2008 04:26:40 |
|
|
|
tmarko
Starting Member
15 Posts |
Posted - 01/22/2008 : 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 |
Edited by - tmarko on 01/22/2008 05:11:05 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/22/2008 : 04:50:38
|
Worth a trySELECT 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" |
|
|
|
tmarko
Starting Member
15 Posts |
Posted - 01/22/2008 : 05:19:12
|
Nope, does not return all values. Your select does not return all TIM_id for every CUH_CUR_id
thanks anyway |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/22/2008 : 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" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
|
|
tmarko
Starting Member
15 Posts |
Posted - 01/23/2008 : 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
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/23/2008 : 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" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/23/2008 : 08:38:27
|
Or try for yourself by changing order for the JOINS.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
tmarko
Starting Member
15 Posts |
Posted - 01/23/2008 : 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)
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 01/23/2008 : 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 |
|
|
|
tmarko
Starting Member
15 Posts |
Posted - 01/23/2008 : 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. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/24/2008 : 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" |
|
|
|
tmarko
Starting Member
15 Posts |
Posted - 01/24/2008 : 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
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/24/2008 : 04:04:08
|
Good luck with performance!
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
tmarko
Starting Member
15 Posts |
Posted - 01/24/2008 : 04:06:44
|
| Yes, that is an issue, but if that is the case, I might put in a temp table |
|
|
| |
Topic |
|
|
|
Left Join problem
