Logic missing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Logic missing

Author

Topic

Hari k
Starting Member

5 Posts

Posted - 2013-11-06 : 06:01:37

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

52326 Posts

Posted - 2013-11-06 : 06:21:21

[code]
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
[/code]

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

Hari k
Starting Member

5 Posts

Posted - 2013-11-07 : 02:19:15

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-07 : 02:41:51

[code]
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
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources