SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Logic missing
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hari k
Starting Member

5 Posts

Posted - 11/06/2013 :  06:01:37  Show Profile  Reply with Quote
Hi I am new to mysql database can any one help to my problem I have three tables like this
select * from device;
Vehiclegroup vehicleName deviceid accountid
------------ ----------- -------- -----------
sms cargo test1 sysadmin
sms cargo1 test2 sysadmin
sms cargo3 test3 sysadmin
select * from Account;
accountid password deviceid
--------- -------- ----------
sysadmin pass test3
sysadmin pass test2
sysadmin pass test1
And finally

mysql> select * from eventdata;
+-----------+-------+----------+----------+
| accountid | speed | date | deviceid |
+-----------+-------+----------+----------+
| sysadmin | 45 | 12/10/13 | test3 |
| sysadmin | 45 | 10/10/13 | test3 |
| sysadmin | 45 | 10/10/13 | test2 |
| sysadmin | 45 | 11/10/13 | test2 |
| sysadmin | 45 | 14/10/13 | test2 |
| sysadmin | 45 | 17/10/13 | test2 |
| sysadmin | 45 | 17/10/13 | test1 |
+-----------+-------+----------+----------+
7 rows in set (0.00 sec)
here i need output table as shown below
vehiclegroup vehiclename deviceid date
sms cargo1 test1 17/10/13
sms cargo2 test2 17/10/13
sms cargo3 test3 12/10/13

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/06/2013 :  06:21:21  Show Profile  Reply with Quote

SELECT *
FROM device d
INNER JOIN Account a
ON a.deviceid = d.deviceid 
AND a.accountid = d.accountid
INNER JOIN eventdata ed
ON ed.accountid = a.accountid 
AND ed.deviceid = a.deviceid 
INNER JOIN (SELECT accountid,deviceid,MAX(date) AS Latest
            FROM eventdata
            GROUP BY accountid,deviceid
           )ed1
ON ed1.accountid = ed.accountid 
AND ed1.deviceid = ed.deviceid 
AND ed1.Latest = ed.date


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Hari k
Starting Member

5 Posts

Posted - 11/07/2013 :  02:19:15  Show Profile  Reply with Quote
Thanks it is working fine.But i have one more problem i need to display output table in ascending order
i.e output table will be look like this
input table1:
select * from device;
Vehiclegroup vehicleName deviceid accountid
------------ ----------- -------- -----------
sms cargo test1 sysadmin
sms cargo1 test2 sysadmin
sms cargo3 test3 sysadmin
input table2:
select * from Account;
accountid password deviceid
--------- -------- ----------
sysadmin pass test3
sysadmin pass test2
sysadmin pass test1
And finally
input table3:
mysql> select * from eventdata;
+-----------+-------+----------+----------+
| accountid | speed | date | deviceid |
+-----------+-------+----------+----------+
| sysadmin | 45 | 12/10/13 | test3 |
| sysadmin | 45 | 10/10/13 | test3 |
| sysadmin | 45 | 10/10/13 | test2 |
| sysadmin | 45 | 31/10/13 | test2 |
| sysadmin | 45 | 14/10/13 | test2 |
| sysadmin | 45 | 17/10/13 | test2 |
| sysadmin | 45 | 17/10/13 | test1 |
+-----------+-------+----------+----------+
7 rows in set (0.00 sec)
Output table:
here i need output table as shown below
vehiclegroup vehiclename deviceid date
sms cargo2 test2 31/10/13
sms cargo1 test1 17/10/13
sms cargo3 test3 12/10/13
previously output will be below

sms cargo1 test1 17/10/13
sms cargo2 test2 17/10/13
sms cargo3 test3 12/10/13


quote:
Originally posted by visakh16


SELECT *
FROM device d
INNER JOIN Account a
ON a.deviceid = d.deviceid 
AND a.accountid = d.accountid
INNER JOIN eventdata ed
ON ed.accountid = a.accountid 
AND ed.deviceid = a.deviceid 
INNER JOIN (SELECT accountid,deviceid,MAX(date) AS Latest
            FROM eventdata
            GROUP BY accountid,deviceid
           )ed1
ON ed1.accountid = ed.accountid 
AND ed1.deviceid = ed.deviceid 
AND ed1.Latest = ed.date


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


TextTextText
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/07/2013 :  02:41:51  Show Profile  Reply with Quote

SELECT *
FROM device d
INNER JOIN Account a
ON a.deviceid = d.deviceid 
AND a.accountid = d.accountid
INNER JOIN eventdata ed
ON ed.accountid = a.accountid 
AND ed.deviceid = a.deviceid 
INNER JOIN (SELECT accountid,deviceid,MAX(date) AS Latest
            FROM eventdata
            GROUP BY accountid,deviceid
           )ed1
ON ed1.accountid = ed.accountid 
AND ed1.deviceid = ed.deviceid 
AND ed1.Latest = ed.date
ORDER BY d.Vehiclegroup,ed.date DESC


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000