CODEWITHSUNDEEP
mysqlsqljoinmultiple-tables
Anand
Anand

Reputation: 5332

Multiply Value From Multiple Tables MySQL

I'm using mySql 5.1. I got in a trouble. The query below is working perfect. I want to replace the constant value 100 with value from database with respect to corresponding SID. But I don't know how to do it. 

INSERT INTO ResultTable(SID,Date,Result) 
    SELECT SID,DATE(`Time`),(MAX(SValue)-MIN(SValue))* 100 
      FROM table1   
  GROUP BY Date(`Time`),SID;


            Table 1
------------------------------------------
SID         Time                SValue
------------------------------------------
7       2013-05-06 12:06:17     5668
8       2013-04-10 16:17:49     11434.9
8       2013-04-10 16:18:54     11435.5
7       2013-04-10 16:21:04     11436.8
7       2013-04-10 16:22:09     11437.4
8       2013-04-10 16:25:24     11439.2


    Table2
---------------------
SID     EValue
---------------------
7            10
8            100


        Result Table
------------------------------------------
SID     Date                Result
------------------------------------------
7       2013-05-06               56680
8       2013-04-10               1143490
8       2013-04-10               1143550
7       2013-04-10               114368
7       2013-04-10               114374
8       2013-04-10               1143920

Existing query : 

    SELECT SID,DATE(`Time`),(MAX(SValue)-MIN(SValue))* 100 
      FROM table1   
  GROUP BY Date(`Time`),SID;

My Query :

      select (h1.EValue * ss.SValue) AS Emission 
        from Table2 h1 
  inner join (
       select SID, (MAX(SValue)-MIN(SValue)) AS SValue 
         from Table1 
     group by Date(`Time`), SID
      ) ss on h1.SID = ss.SID

But my query gives some extra values. My questions are

  1. What I'm doing wrong here?

  2. Why extra values coming?

  3. Is there any better way to do this?

Any help will be appreciated..

Upvotes: 0

Views: 1713

Answers (2)

Sylvain Leroux
Sylvain Leroux

Reputation: 52030

Do this return the expected result (I type without testing -- so it could be some typos -- shame on me)?

    SELECT T1.SID,DATE(T1.`Time`),(MAX(T1.SValue)-MIN(T1.SValue))* T2.EValue
        FROM table1 AS T1, Table2 AS T2
        WHERE T1.SID = T2.SID
        GROUP BY Date(T1.`Time`),T1.SID;

Upvotes: 1

Tom
Tom

Reputation: 6663

I think this is what you are looking for. I simply join to table2 and get the EValue from there and replaced the 100. 

SELECT  h1.SID, DATE(`Tm`), 
        (MAX(SValue)-MIN(SValue))* ss.EValue 

FROM    table1 as h1

        JOIN table2 as ss
        ON h1.SID = ss.SID

GROUP BY Date(`Tm`), h1.SID;

I think the reason you were getting "extra" values was because you were grouping by date and SID in your sub-query, but you weren't showing the date in your main query. So you would have multiple results for SIDs that had different dates.

Upvotes: 1

Related Questions