From: Anupam Karmarkar Date: October 3 2011 6:36am Subject: Re: Join 2 tables and compare then calculate something List-Archive: http://lists.mysql.com/mysql/225899 Message-Id: <1317623765.10954.YahooMailNeo@web94708.mail.in2.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="800968924-613376425-1317623765=:10954" --800968924-613376425-1317623765=:10954 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Try out query with UNION also=0A=0Aselect 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=0A= =0A=0A=0A________________________________=0AFrom: Gian Karlo C =0ATo: mysql@stripped=0ASent: Sunday, 2 October 2011 12:49 = PM=0ASubject: Join 2 tables and compare then calculate something=0A=0AHi Al= l,=0A=0AI decided to join and write to the list hoping someone could help a= nd shed a=0Alight on me.=0A=0AHere's the scenario.=0A=0AI 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.=0A=0ATable 1=0AName, IPAddress, Des= cription, Issue, Severity, Timestamp, DateReceived=0A=0ATable 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.=0A=0Aselect 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 =3D=0ATable2.Description AND Table1.Issue =3D Table2.Issue= AND Table1.Severity =3D=0ATable2.Severity AND Table1.Timestamp =3D Table2.= Timestamp=A0 group by 1;=0A=0AI 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.=0A=0AUsing 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.=0A=0ATable1=0ADateReceived =3D "10:05"=0A=0ATable2=0ADateReceived =3D = "10:15"=0A=0ASo the computation is to get the difference between DateReceiv= ed which the=0Aresult should be 10 minutes.=0A=0AHow 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.=0A=0AThanks in advance. --800968924-613376425-1317623765=:10954--