Different output after using INNER JOIN [Solved]

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 2012 Forums

Transact-SQL (2012)

Different output after using INNER JOIN [Solved]

Author

Topic

partap26
Starting Member

27 Posts

Posted - 2013-06-07 : 00:50:29

SELECT SUM(eb_time) as eb_time FROM alarm_time where site_id='S001';
The output is :

eb_time

12175

SELECT avg(dc_volt) as dc_volt, avg(curr_temp) as curr_temp FROM avg_values WHERE site_id='S001';

The output is:

dc_volt curr_temp

?43.305000 21.580000

SELECT sd.site_id, sd.site_name,
avg(av.dc_volt) as dc_volt, avg(av.curr_temp) as curr_temp,
SUM(at.eb_time) as eb_time
FROM site_details sd
INNER JOIN avg_values av ON av.site_id = sd.site_id
INNER JOIN alarm_time at ON at.site_id = sd.site_id
WHERE sd.site_id='S001'
GROUP BY sd.site_id, sd.site_name;
The output is:

site_id site_name dc_volt curr_temp eb_time

S001 Prade 43.305000 21.580000 48700

But the output should be

site_id site_name dc_volt curr_temp eb_time

S001 Prade 43.305000 21.580000 12175 // I am not getting the eb_time properly........

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-07 : 01:00:41

I think relationship between alarm_time and site_details is one to many which is causing this.

Is this any better?


SELECT sd.site_id, sd.site_name, 
avg(av.dc_volt) as dc_volt, avg(av.curr_temp) as curr_temp,
SUM(at.eb_time) as eb_time
FROM site_details sd
INNER JOIN avg_values av ON av.site_id = sd.site_id
INNER JOIN (SELECT *,ROW_NUMBER() OVER (PARTITION BY site_id ORDER BY site_id) AS Seq FROM alarm_time) at 
ON at.site_id = sd.site_id
AND at.Seq=1

WHERE sd.site_id='S001'
GROUP BY sd.site_id, sd.site_name;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

partap26
Starting Member

27 Posts

Posted - 2013-06-07 : 01:02:56

According to your query the eb_time output is 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-07 : 01:52:58

Ok in that case please show us some sample data from your tables for a value of site_id

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

partap26
Starting Member

27 Posts

Posted - 2013-06-07 : 02:11:31

Okay, I am going to send you some sample data with queries

SELECT * FROM site_details;
Site Id Site Name
S001 Prade
S002 Moti Bazar
S003 Railway Phatak
S004 JDA Parking

SELECT site_id, eb_time, bb_time, dg_time from alarm_time where site_id='S001';

S Id eb_time bb_time dg_time
S001 0 91 0
S001 501 2089 1297
S001 11232 0 10303
S001 2657 0 0
S001 417 1505 193

SELECT site_id, SUM(eb_time) as eb_time,
SUM(bb_time) as bb_time,
SUM(dg_time) as dg_time
FROM alarm_time
WHERE site_id='S001'
GROUP BY site_id;

S Id eb_time bb_time dg_time
S001 14807 3685 11793

SELECT site_id, dc_volt, curr_temp from avg_values WHERE site_id='S001';

S Id dc_volt curr_temp
S001 43.23 21.55
S001 43.99 22.77
S001 43.00 21.00
S001 43.00 21.00

SELECT site_id, avg(dc_volt) as dc_volt,
avg(curr_temp) as curr_temp
FROM avg_values
WHERE site_id='S001'
GROUP BY site_id;

S Id dc_volt curr_temp
S001 43.305000 21.580000

Here is the problem in this query

SELECT sd.site_id, sd.site_name,
avg(av.dc_volt) as dc_volt, avg(av.curr_temp) as curr_temp,
SUM(at.eb_time) as eb_time
FROM site_details sd
INNER JOIN avg_values av ON av.site_id = sd.site_id
INNER JOIN alarm_time at ON at.site_id = sd.site_id
WHERE sd.site_id='S001'
GROUP BY sd.site_id, sd.site_name;

S Id S Name Dc Volt Curr Temp eb_time
S001 Prade 43.305000 21.580000 59228

The eb_time must be 14807

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-07 : 02:18:49

[code]
SELECT sd.site_id, sd.site_name,
av.dc_volt,
av.curr_temp,
at.eb_time
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
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
WHERE sd.site_id='S001'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

partap26
Starting Member

27 Posts

Posted - 2013-06-07 : 02:28:59

Yes Your answer is correct........Is there any option to make a mark that your answer is correct

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-07 : 02:35:27

you can just modify the title and prefix [Solved]:

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources