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_averageHistoENDfrom TIM_TP TIMleft join CUH_CH CUH on TIM.TIM_id=CUH.CUH_TIM_id,CUR_C CURwhere CUR.CUR_id=CUH.CUH_CUR_idorder by CUR.CUR_id,TIM.TIM_idI 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 nullCODO ERGO SUM |
|
|
tmarko
Starting Member
15 Posts |
Posted - 2008-01-21 : 19:45:55
|
Thanks, MVJ, so evident when I see it :) |
|
|
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 |
|
|
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 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-01-22 : 04:50:38
|
Worth a trySELECT cur.CUR_id, tim.TIM_id, COALESCE(cuh.CUH_averageHisto, cur.CUR_average)FROM TIM_TP AS timLEFT JOIN CUH_CH AS cuh ON cuh.CUH_TIM_id = tim.TIM_idLEFT JOIN CUR_C AS cur ON cur.CUR_id = cuh.CUH_CUR_idORDER BY cur.CUR_id, tim.TIM_id E 12°55'05.25"N 56°04'39.16" |
|
|
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_idthanks anyway |
|
|
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" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
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 valueslike:NULL 1448 NULLNULL 1449 NULL2 470 .12000000002 471 .1200000000ButI 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_idlike 2 1449 0.112 469 0.112 470 .12000000002 471 .12000000003 1449 0.103 1449 0.113 469 0.113 470 0.12Table CUH_CH looks likeCUH_CUR_id,CUH_TIM_id,CUH_averageHistoex.1,400,0.131,401,0.141,403,0.15....1,1500,0.192,400,0.192,401,0.20...Table CUR_CurrencyCUR_id,CUR_average,ex.1,0.1332,0.1443,0.1455Table TIM_TPTIM_id400401402...14491500Hope this explains better what I would like to doKind regards |
|
|
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" |
|
|
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" |
|
|
tmarko
Starting Member
15 Posts |
Posted - 2008-01-23 : 12:07:03
|
Ok Peso--drop table CUR_CCREATE 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_CHCREATE 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
52326 Posts |
Posted - 2008-01-23 : 12:39:24
|
quote: Originally posted by tmarko Ok Peso--drop table CUR_CCREATE 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_CHCREATE 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 chINNER JOIN CUR_C ccON cc.CUR_id=ch.CUH_CUR_idLEFT OUTER JOIN TIM_TP ttON tt.TIM_id=ch.CUH_TIM_idorder by cc.CUR_id,tt.TIM_id |
|
|
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. |
|
|
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" |
|
|
tmarko
Starting Member
15 Posts |
Posted - 2008-01-24 : 03:08:12
|
I finally got help from a collegueThe solutionselect 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
30421 Posts |
Posted - 2008-01-24 : 04:04:08
|
Good luck with performance! E 12°55'05.25"N 56°04'39.16" |
|
|
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 |
|
|
|
|
|