Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011

    Unanswered: Joining 2 tables and compare data then calculate

    Hi All,

    I decided to join and write to the list hoping someone could help and shed a
    light on me.

    Here's the scenario.

    I have a database running in mysql 5.x in Centos 5. The database has 2
    tables that is almost identical with some additional fields.

    Table 1
    Name, IPAddress, Description, Issue, Severity, Timestamp, DateReceived

    Table 2
    Name, IPAddress, Description, Issue, Severity, Timestamp, DataReceived,

    Here's my SQL statement to compare both tables if fields data are the same
    then consider it as a valid record.

    select Table1.Name, Table1.IPAddress, Table1.Description, Table1.Issue,
    Table1.Severity, Table1.Timestamp FROM Table1 LEFT JOIN Table2 ON
    Table1.Name = Table2.Name WHERE Table1.Name = Table2.Name AND
    Table1.IPAddress = Table2.IPAddress AND Table1.Description =
    Table2.Description AND Table1.Issue = Table2.Issue AND Table1.Severity =
    Table2.Severity AND Table1.Timestamp = Table2.Timestamp group by 1;

    I need to compare Name, IPAddress, Description, Issue, Severity and
    Timestamp to consider as I valid data then I group it so that only one
    record will show although there is no duplicate entry on the results. Just
    want to make sure.

    Using that SQL statement I was able to get and compare data (which I don't
    know if this is a good approach), now when I get a valid results, I want to
    compute the DateReceived.

    DateReceived = "10:05"

    DateReceived = "10:15"

    So the computation is to get the difference between DateReceived which the
    result should be 10 minutes.

    How would I add that computation to my existing SQL statement and maybe
    someone suggest a good approach with regards to my current statement.

    Thanks in advance.

  2. #2
    Join Date
    Oct 2011
    based on my research, i found that I can use timediff() function. but where should i insert the timediff in my sql statement?

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    try researching in the MySQL manual for the version you are using
    MySQL :: MySQL 5.5 Reference Manual :: 11.7 Date and Time Functions

    SELECT my, comma, separated, column, list, TIMEDIFF(laterdatetimevalue,olderdatetimevalue) as elapsedtime FROM my table
    WHERE somecolumn = blah
    ORDER BY some, column, list
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2011
    thank you it works now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts