My JOIN bug - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

My JOIN bug

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2008-06-20 : 14:45:58

Hi,
I have a simple join that's suppossed to get the right records, but it doesn't. Here's it -

select st_zone_id from station_info st, station_param_info sp
where st.st_type = '4'and st.st_status = '2' sp.sp_collectiontype = '1' and st.st_zone_id = sp.sp_zone_id

It gives me records that have different collection types, not only 1, as it should. Any ideas why?
Thanks.

sqlbug

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 14:49:20

[code]select st.st_zone_id
from station_info as st
inner join station_param_info as sp on sp.sp_zone_id = st.st_zone_id
where st.st_type = '4'
and st.st_status = '2'
and sp.sp_collectiontype = '1'[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 14:50:02

[code]select st_zone_id
from station_info st
inner join station_param_info sp
on sp.sp_collectiontype = '1'
and st.st_zone_id = sp.sp_zone_id
where st.st_type = '4'and st.st_status = '2' [/code]

if this is not what you expect can you illustrate what you want with some sample data?

sqlbug
Posting Yak Master

201 Posts

Posted - 2008-06-20 : 15:47:30

Thanks to both of you. I think the problem lies somewhere else not clearly mentioned in my first post, so I'll go home and post it with more information - so you guys can help.

Please keep an eye on it.

sqlbug

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-21 : 01:30:42

quote:
Originally posted by sqlbug

Thanks to both of you. I think the problem lies somewhere else not clearly mentioned in my first post, so I'll go home and post it with more information - so you guys can help.

Please keep an eye on it.

sqlbug

Sure. While posting some sample data would really help.

sqlbug
Posting Yak Master

201 Posts

Posted - 2008-06-24 : 15:47:32

Hello,

Sorry for the delay. Here's the whole query:
--------------------------------------------------------------------
select z.zone_owner, z.zone_name_full, count(st.st_zone_id) total_station, z.zone_id from station_info stn, zone_info z where st.st_type = '4' and st.st_status = '2' st.st_name Not Like ('%Portable%') and st.st_zone_id = z.zone_id and st.st_zone_id != '19' and st.st_zone_id != '18' and st.st_zone_id != '04' and st.st_zone_id != '03' and st.st_zone_id != '00' and st.st_zone_id != '05' and st.st_zone_id in
(select st_zone_id from station_info st, station_param_info sp
where st.st_type = '4'and st.st_status = '2' and sp.sp_collectiontype = '1' and st.st_zone_id = sp.sp_zone_id)
------------------------------------------------------------------
So, In the second part, I wanted to filter the count by sp_collectiontype as well, but the first select is not filtering it by sp_collectiontype - so I get a wrong output. If I try to join all 3 tables, I get much bigger counts, clearly incorrect.
Sample result:
zone_owner zone_name_full total_station zone_id
---------- -------------- ------------- -------
Kali Corp. abcd zone 26 (correct: 1)* 13

*because its not filtering by sp_collectiontype
If you are not keeping track of this post because its been few days, I will make another post.
Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources