SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Left Join problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tmarko
Starting Member

15 Posts

Posted - 01/21/2008 :  19:01:04  Show Profile  Reply with Quote
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
7020 Posts

Posted - 01/21/2008 :  19:22:08  Show Profile  Reply with Quote
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 - 01/21/2008 :  19:45:55  Show Profile  Reply with Quote
Thanks, MVJ, so evident when I see it :)
Go to Top of Page

tmarko
Starting Member

15 Posts

Posted - 01/21/2008 :  20:06:30  Show Profile  Reply with Quote
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
Go to Top of Page

tmarko
Starting Member

15 Posts

Posted - 01/22/2008 :  04:29:34  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 01/22/2008 :  04:50:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 01/22/2008 :  05:19:12  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 01/22/2008 :  07:09:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30265 Posts

Posted - 01/22/2008 :  07:19:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 01/23/2008 :  08:26:32  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 01/23/2008 :  08:34:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30265 Posts

Posted - 01/23/2008 :  08:38:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 01/23/2008 :  12:07:03  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/23/2008 :  12:39:24  Show Profile  Reply with Quote
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 - 01/23/2008 :  13:03:33  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 01/24/2008 :  02:27:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 01/24/2008 :  03:08:12  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 01/24/2008 :  04:04:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 01/24/2008 :  04:06:44  Show Profile  Reply with Quote
Yes, that is an issue, but if that is the case, I might put in a temp table
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000