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.
Author |
Topic |
partap26
Starting Member
27 Posts |
Posted - 2013-06-24 : 03:29:26
|
The commands contains following informations:-SELECT site_id, site_name, tech_name, tech_contact, admin_id, cluster_id FROM site_details; site_id site_name tech_name tech_contact site_address admin_id cluster_idS001 Prade Mohinder 9419998988 Ranbir Library SalathiaSoftwares KCHSELECT * FROM cluster_info;circle_id cluster_id cluster_nameJK KCH Kacchi ChowniJK Prd PradeJK TNGR Trikuta NagarSELECT * FROM circle_info;zone_id circle_id circle_nameN JK Jammu and KashmirSELECT * FROM zone_info;zone_id zone_nameN NorthE East Now the following query showing error as Msg 207, Level 16, State 1, Line 48Invalid column name 'circle_id'.Msg 207, Level 16, State 1, Line 53Invalid column name 'zone_id'.SELECT sd.site_id, sd.site_name, cl.cluster_id, ci.circle_id, z.zone_id,av.dc_volt, av.curr_temp,at.eb_time, at.bb_time, at.dg_time, at.hrt_time, at.dfs_time, at.dft_time,at.llop_time,at.door_open_time, at.fire_time, at.spare_time,ac.eb_fail_count, ac.dg_count, ac.dc_low_count, ac.hrt_count, ac.dfs_count, ac.dft_count,ac.llop_count, ac.fire_count, ac.door_open_count, ac.spare_countFROM site_details sdINNER JOIN( SELECT site_id, SUM(eb_time) as eb_time, SUM(bb_time) as bb_time, SUM(dg_time) as dg_time, SUM(hrt_time) as hrt_time, SUM(dfs_time) as dfs_time, SUM(dft_time) as dft_time, SUM(llop_time) as llop_time, SUM(fire_time) as fire_time, SUM(door_open_time) as door_open_time, SUM(eb_r_time) as eb_r_time, SUM(dc_low_time) as dc_low_time, SUM(spare_time) as spare_time, MIN(at_date) as from_date, MAX(at_date) as to_date FROM alarm_time GROUP BY site_id )at ON at.site_id = sd.site_id INNER JOIN( SELECT site_id, avg(dc_volt) as dc_volt, avg(curr_temp) as curr_temp FROM avg_values GROUP BY site_id )av ON av.site_id = sd.site_id INNER JOIN( SELECT site_id, SUM(eb_fail_count) as eb_fail_count, SUM(dg_count) as dg_count, SUM(dc_low_count) as dc_low_count, SUM(hrt_count) as hrt_count, SUM(dfs_count) as dfs_count, SUM(dft_count) as dft_count, SUM(llop_count) as llop_count, SUM(fire_count) as fire_count, SUM(door_open_count) as door_open_count, SUM(spare_count) as spare_count FROM alarm_count GROUP BY site_id )ac ON ac.site_id = sd.site_idINNER JOIN( SELECT cluster_id FROM cluster_info )cl ON cl.cluster_id = sd.cluster_idINNER JOIN( SELECT circle_id FROM circle_info )ci ON ci.circle_id = cl.circle_idINNER JOIN( SELECT zone_id FROM zone_info )z ON z.zone_id = ci.zone_id; The above query is working fine without using last three inner joins. After adding third last join(cluster_info), it also works fine but after adding second last it is not working fine.....please provide the solution. I am unable to recognize the problem.... |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-06-24 : 03:36:24
|
[code]INNER JOIN( SELECT cluster_id FROM cluster_info )cl ON cl.cluster_id = sd.cluster_idINNER JOIN( SELECT circle_id FROM circle_info )ci ON ci.circle_id = cl.circle_id[/code]there isn't a circle_id column from cl derived table[code]INNER JOIN( SELECT circle_id FROM circle_info )ci ON ci.circle_id = cl.circle_idINNER JOIN( SELECT zone_id FROM zone_info )z ON z.zone_id = ci.zone_id;[/code]Similarly for the zone_id KH[spoiler]Time is always against us[/spoiler] |
|
|
partap26
Starting Member
27 Posts |
Posted - 2013-06-24 : 03:39:35
|
SELECT * FROM cluster_info;circle_id cluster_id cluster_nameJK KCH Kacchi ChowniJK Prd PradeJK TNGR Trikuta Nagar |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-06-24 : 03:40:59
|
perhaps it should be just simplyINNER JOIN cluster_info cl ON cl.cluster_id = sd.cluster_idINNER JOIN circle_info ci ON ci.circle_id = cl.circle_idINNER JOIN zone_info z ON z.zone_id = ci.zone_id; not sure what is your intention of joining to these 3 tables.. as you are not referencing them at all in the SELECT clause KH[spoiler]Time is always against us[/spoiler] |
|
|
partap26
Starting Member
27 Posts |
Posted - 2013-06-24 : 03:43:29
|
hahahahaaaaaaaa... this is working fine but i want to do this with that method where i am facing problem.... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-24 : 03:48:21
|
quote: Originally posted by partap26 hahahahaaaaaaaa... this is working fine but i want to do this with that method where i am facing problem....
your method is equivalent to what Tan posted as you're just doing a direct select inside those derived tables. So you may as well use tables directly instead as shoqn by Tanif you still want to stick to your way, use like thisSELECT sd.site_id, sd.site_name, cl.cluster_id, ci.circle_id, z.zone_id,av.dc_volt, av.curr_temp,at.eb_time, at.bb_time, at.dg_time, at.hrt_time, at.dfs_time, at.dft_time,at.llop_time,at.door_open_time, at.fire_time, at.spare_time,ac.eb_fail_count, ac.dg_count, ac.dc_low_count, ac.hrt_count, ac.dfs_count, ac.dft_count,ac.llop_count, ac.fire_count, ac.door_open_count, ac.spare_countFROM site_details sdINNER JOIN( SELECT site_id, SUM(eb_time) as eb_time, SUM(bb_time) as bb_time, SUM(dg_time) as dg_time, SUM(hrt_time) as hrt_time, SUM(dfs_time) as dfs_time, SUM(dft_time) as dft_time, SUM(llop_time) as llop_time, SUM(fire_time) as fire_time, SUM(door_open_time) as door_open_time, SUM(eb_r_time) as eb_r_time, SUM(dc_low_time) as dc_low_time, SUM(spare_time) as spare_time, MIN(at_date) as from_date, MAX(at_date) as to_date FROM alarm_time GROUP BY site_id )at ON at.site_id = sd.site_id INNER JOIN( SELECT site_id, avg(dc_volt) as dc_volt, avg(curr_temp) as curr_temp FROM avg_values GROUP BY site_id )av ON av.site_id = sd.site_id INNER JOIN( SELECT site_id, SUM(eb_fail_count) as eb_fail_count, SUM(dg_count) as dg_count, SUM(dc_low_count) as dc_low_count, SUM(hrt_count) as hrt_count, SUM(dfs_count) as dfs_count, SUM(dft_count) as dft_count, SUM(llop_count) as llop_count, SUM(fire_count) as fire_count, SUM(door_open_count) as door_open_count, SUM(spare_count) as spare_count FROM alarm_count GROUP BY site_id )ac ON ac.site_id = sd.site_idINNER JOIN( SELECT cluster_id ,circle_id,zone_id FROM cluster_info )cl ON cl.cluster_id = sd.cluster_idINNER JOIN( SELECT circle_id FROM circle_info )ci ON ci.circle_id = cl.circle_idINNER JOIN( SELECT zone_id FROM zone_info )z ON z.zone_id = ci.zone_id; ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
partap26
Starting Member
27 Posts |
Posted - 2013-06-24 : 03:53:28
|
visakh16Please check first three queries so that table column name clear... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-24 : 03:56:32
|
quote: Originally posted by partap26 visakh16Please check first three queries so that table column name clear...
What? I just copy pasted your query and included the required columns. do you mean you dont have those columns in table? then obviously you wont be able to join them in the way as you posted!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-06-24 : 03:58:14
|
quote: Originally posted by partap26 hahahahaaaaaaaa... this is working fine but i want to do this with that method where i am facing problem....
If you want to use your original derived table way, you have to include all the columns that the query is referencing as what Visakh had show you.For your query, it is not necessary to use derived table for the 3 info tables as it is just a simple table and no complex query like what you have for at, av or ac. KH[spoiler]Time is always against us[/spoiler] |
|
|
partap26
Starting Member
27 Posts |
Posted - 2013-06-24 : 03:58:42
|
SELECT * FROM cluster_info;circle_id cluster_id cluster_nameJK KCH Kacchi ChowniJK Prd PradeJK TNGR Trikuta NagarSELECT * FROM circle_info;zone_id circle_id circle_nameN JK Jammu and KashmirSELECT * FROM zone_info;zone_id zone_nameN NorthE Eastand i have already tested the query with the way posted above |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-06-24 : 04:00:39
|
quote: Originally posted by partap26 visakh16Please check first three queries so that table column name clear...
You do know that we don't have your tables or data in our database right ? We have to manually inspect the query to do that. It is much easier for you to check and verify by simply press the F5 and the query compiler will verify that much quicker than any of us. KH[spoiler]Time is always against us[/spoiler] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-24 : 04:00:44
|
quote: Originally posted by partap26 SELECT * FROM cluster_info;circle_id cluster_id cluster_nameJK KCH Kacchi ChowniJK Prd PradeJK TNGR Trikuta NagarSELECT * FROM circle_info;zone_id circle_id circle_nameN JK Jammu and KashmirSELECT * FROM zone_info;zone_id zone_nameN NorthE Eastand i have already tested the query with the way posted above
You wont be able to add the three joins if your three tables are as above as there's no common columns between the tables which will help you to link them together and that was the reason you were getting those invalid column errors------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-06-24 : 04:02:21
|
quote: Originally posted by partap26 SELECT * FROM cluster_info;circle_id cluster_id cluster_nameJK KCH Kacchi ChowniJK Prd PradeJK TNGR Trikuta NagarSELECT * FROM circle_info;zone_id circle_id circle_nameN JK Jammu and KashmirSELECT * FROM zone_info;zone_id zone_nameN NorthE Eastand i have already tested the query with the way posted above
What is your point here ? Your big query does not produce the result that you are expected ? KH[spoiler]Time is always against us[/spoiler] |
|
|
partap26
Starting Member
27 Posts |
Posted - 2013-06-24 : 04:05:28
|
My point is here:Thanks for cooperating with me :) |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-06-24 : 04:05:30
|
ah. .. i think i see what you mean ..... it should beINNER JOIN( SELECT cluster_id ,circle_id FROM cluster_info )cl ON cl.cluster_id = sd.cluster_idINNER JOIN( SELECT circle_id , zone_id FROM circle_info )ci ON ci.circle_id = cl.circle_idINNER JOIN( SELECT zone_id FROM zone_info )z ON z.zone_id = ci.zone_id; Visakh wrongly put the zone_id in the wrong query KH[spoiler]Time is always against us[/spoiler] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-24 : 04:08:25
|
ah...nice catch Tan ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
partap26
Starting Member
27 Posts |
Posted - 2013-06-24 : 04:10:26
|
khtanIt is solved now by your code |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-06-24 : 04:21:09
|
welcome KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|