Join 2 tables and compare then calculate something

--bcaec520f27d97129d04ae4bad28
Content-Type: text/plain; charset=ISO-8859-1

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,
Owner

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.

Table1
DateReceived = "10:05"

Table2
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.

--bcaec520f27d97129d04ae4bad28--

Re: Join 2 tables and compare then calculate something

--800968924-613376425-1317623765=:10954
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Try out query with UNION also select A,B,C from=0A(select A,B,C from X=
=0AUNION=0Aselect A,B,C from Y)=0Agroup by A,B,C=0Ahaving count(*)>1 =
=0A________________________________=0AFrom: Gian Karlo C mail.com>=0ATo: mysql@lists.mysql.com=0ASent: Sunday, 2 October 2011 12:49 =
PM=0ASubject: Join 2 tables and compare then calculate something Hi Al=
l, I decided to join and write to the list hoping someone could help a=
nd shed a=0Alight on me. Here's the scenario. I have a database r=
unning in mysql 5.x in Centos 5. The database has 2=0Atables that is almost=
identical with some additional fields. Table 1=0AName, IPAddress, Des=
cription, Issue, Severity, Timestamp, DateReceived Table 2=0AName, IPA=
ddress, Description, Issue, Severity, Timestamp, DataReceived,=0AOwner=0A=
=0AHere's my SQL statement to compare both tables if fields data are the sa=
me=0Athen consider it as a valid record. select Table1.Name, Table1.IP=
Address, Table1.Description, Table1.Issue,=0ATable1.Severity, Table1.Timest=
amp FROM Table1 LEFT JOIN Table2 ON=0ATable1.Name =3D Table2.Name WHERE Tab=
le1.Name =3D Table2.Name AND=0ATable1.IPAddress =3D Table2.IPAddress AND Ta=
ble1.Description = Table2.Description AND Table1.Issue =3D Table2.Issue=
AND Table1.Severity = Table2.Severity AND Table1.Timestamp =3D Table2.=
Timestamp=A0 group by 1; I need to compare Name, IPAddress, Descriptio=
n, Issue, Severity and=0ATimestamp to consider as I valid data then I group=
it so that only one=0Arecord will show although there is no duplicate entr=
y on the results. Just=0Awant to make sure. Using that SQL statement I=
was able to get and compare data (which I don't=0Aknow if this is a good a=
pproach), now when I get a valid results, I want to=0Acompute the DateRecei=
ved. Table1=0ADateReceived =3D "10:05" Table2=0ADateReceived =3D =
"10:15" So the computation is to get the difference between DateReceiv=
ed which the=0Aresult should be 10 minutes. How would I add that compu=
tation to my existing SQL statement and maybe=0Asomeone suggest a good appr=
oach with regards to my current statement. Thanks in advance.
--800968924-613376425-1317623765=:10954--