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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Error while using inner joins[Soloved]

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_id
S001 Prade Mohinder 9419998988 Ranbir Library SalathiaSoftwares KCH

SELECT * FROM cluster_info;
circle_id cluster_id cluster_name
JK KCH Kacchi Chowni
JK Prd Prade
JK TNGR Trikuta Nagar


SELECT * FROM circle_info;
zone_id circle_id circle_name
N JK Jammu and Kashmir


SELECT * FROM zone_info;
zone_id zone_name
N North
E East




Now the following query showing error as
Msg 207, Level 16, State 1, Line 48
Invalid column name 'circle_id'.
Msg 207, Level 16, State 1, Line 53
Invalid 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_count
FROM site_details sd
INNER 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_id
INNER JOIN(
SELECT cluster_id
FROM cluster_info
)cl
ON cl.cluster_id = sd.cluster_id
INNER JOIN(
SELECT circle_id
FROM circle_info
)ci
ON ci.circle_id = cl.circle_id
INNER 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_id
INNER 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_id
INNER 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]

Go to Top of Page

partap26
Starting Member

27 Posts

Posted - 2013-06-24 : 03:39:35
SELECT * FROM cluster_info;
circle_id cluster_id cluster_name
JK KCH Kacchi Chowni
JK Prd Prade
JK TNGR Trikuta Nagar
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-24 : 03:40:59
perhaps it should be just simply

INNER JOIN cluster_info cl ON cl.cluster_id = sd.cluster_id
INNER JOIN circle_info ci ON ci.circle_id = cl.circle_id
INNER 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]

Go to Top of Page

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....
Go to Top of Page

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 Tan
if you still want to stick to your way, use like this



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_count
FROM site_details sd
INNER 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_id
INNER JOIN(
SELECT cluster_id ,circle_id,zone_id
FROM cluster_info
)cl
ON cl.cluster_id = sd.cluster_id
INNER JOIN(
SELECT circle_id
FROM circle_info
)ci
ON ci.circle_id = cl.circle_id
INNER JOIN(
SELECT zone_id
FROM zone_info
)z
ON z.zone_id = ci.zone_id;





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

partap26
Starting Member

27 Posts

Posted - 2013-06-24 : 03:53:28
visakh16
Please check first three queries so that table column name clear...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 03:56:32
quote:
Originally posted by partap26

visakh16
Please 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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]

Go to Top of Page

partap26
Starting Member

27 Posts

Posted - 2013-06-24 : 03:58:42
SELECT * FROM cluster_info;
circle_id cluster_id cluster_name
JK KCH Kacchi Chowni
JK Prd Prade
JK TNGR Trikuta Nagar


SELECT * FROM circle_info;
zone_id circle_id circle_name
N JK Jammu and Kashmir


SELECT * FROM zone_info;
zone_id zone_name
N North
E East


and i have already tested the query with the way posted above
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-24 : 04:00:39
quote:
Originally posted by partap26

visakh16
Please 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]

Go to Top of Page

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_name
JK KCH Kacchi Chowni
JK Prd Prade
JK TNGR Trikuta Nagar


SELECT * FROM circle_info;
zone_id circle_id circle_name
N JK Jammu and Kashmir


SELECT * FROM zone_info;
zone_id zone_name
N North
E East


and 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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_name
JK KCH Kacchi Chowni
JK Prd Prade
JK TNGR Trikuta Nagar


SELECT * FROM circle_info;
zone_id circle_id circle_name
N JK Jammu and Kashmir


SELECT * FROM zone_info;
zone_id zone_name
N North
E East


and 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]

Go to Top of Page

partap26
Starting Member

27 Posts

Posted - 2013-06-24 : 04:05:28
My point is here:

Thanks for cooperating with me :)
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-24 : 04:05:30
ah. .. i think i see what you mean ..... it should be

INNER JOIN(
SELECT cluster_id ,circle_id
FROM cluster_info
)cl
ON cl.cluster_id = sd.cluster_id
INNER JOIN(
SELECT circle_id , zone_id
FROM circle_info
)ci
ON ci.circle_id = cl.circle_id
INNER 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]

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 04:08:25
ah...nice catch Tan

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

partap26
Starting Member

27 Posts

Posted - 2013-06-24 : 04:10:26
khtan

It is solved now by your code
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-24 : 04:21:09
welcome


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -