MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Anupam Karmarkar Date:October 3 2011 6:36am
Subject:Re: Join 2 tables and compare then calculate something
View as plain text  
Try out query with UNION also

select A,B,C from
(select A,B,C from X
select A,B,C from Y)
group by A,B,C
having count(*)>1

From: Gian Karlo C <webdev.gk@stripped>
To: mysql@stripped
Sent: Sunday, 2 October 2011 12:49 PM
Subject: Join 2 tables and compare then calculate something

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.
Join 2 tables and compare then calculate somethingGian Karlo C2 Oct
  • Re: Join 2 tables and compare then calculate somethingAnupam Karmarkar3 Oct