compsol1993
asked on
MySQL Inner Join on Same Table
Hello,
I have the following table structure.
DeviceNumber - int,
Volume - double,
Timestamp - datetime
I'd like to create a query that produces the difference of two volumes in the same row. The result of the query should provide
VolumeOfId#1, VolumeofID#2, VolumeofID#3, VolumeofID#4, VolumeofID#5, VolumeofID#6, Timestamp
The six ID's can be hard quoted in the query for this example.
I'm familar with Joins but unsure how to execute a join on the same table, and more so on how to produce the six column result.
Any pointers?
Thanks!
I have the following table structure.
DeviceNumber - int,
Volume - double,
Timestamp - datetime
I'd like to create a query that produces the difference of two volumes in the same row. The result of the query should provide
VolumeOfId#1, VolumeofID#2, VolumeofID#3, VolumeofID#4, VolumeofID#5, VolumeofID#6, Timestamp
The six ID's can be hard quoted in the query for this example.
I'm familar with Joins but unsure how to execute a join on the same table, and more so on how to produce the six column result.
Any pointers?
Thanks!
Not sure where the difference comes in but this should get you started
SELECT v1.volume, v2.volume, v3.volume, v4.volume, v5.volume, v6.volume, v1.timestamp
FROM volume AS v1, volume AS v2, volume AS v3, volume AS v4, volume AS v5, volume AS v6
WHERE v1.id = 1 AND v2.id = 2 AND v3.id = 3 AND v4.id = 4 AND v5.id=5 AND v6.id = 6;
Sample data from your table and the desired results would help us understand your intent.
ASKER
Thanks for the feedback everyone, I should have provided some more detal:
Lets say I have this sample data:
Device Number - Volume - Timestamp
300001 - 1 - 2013-01-01
300002 - 2 - 2013-01-01
300003 - 3 - 2013-01-01
300004 - 4 - 2013-01-01
300005 - 5 - 2013-01-01
300006 - 6 - 2013-01-01
300001 - 7 - 2013-01-02
300002 - 8 - 2013-01-02
300003 - 9 - 2013-01-02
300004 - 10 - 2013-01-02
300005 - 11 - 2013-01-02
300006 - 12 - 2013-01-02
...
I would like the output of the query to be:
Vol300001 - Vol300002 - Vol300003 - Vol300004 - Vol300005 - Vol 300006 - Timestamp
1 - 2 - 3 - 4 - 5 - 6 2013-01-01
7 - 8 - 9 - 10 - 11 - 12 2013-01-02
When I mentioned difference I may want to do some some math on those 6 values, but I've done that before.
I tried Julian H's suggestion, and get an empty set as the result. Not sure why, I'll play with that some more.
Thanks
Lets say I have this sample data:
Device Number - Volume - Timestamp
300001 - 1 - 2013-01-01
300002 - 2 - 2013-01-01
300003 - 3 - 2013-01-01
300004 - 4 - 2013-01-01
300005 - 5 - 2013-01-01
300006 - 6 - 2013-01-01
300001 - 7 - 2013-01-02
300002 - 8 - 2013-01-02
300003 - 9 - 2013-01-02
300004 - 10 - 2013-01-02
300005 - 11 - 2013-01-02
300006 - 12 - 2013-01-02
...
I would like the output of the query to be:
Vol300001 - Vol300002 - Vol300003 - Vol300004 - Vol300005 - Vol 300006 - Timestamp
1 - 2 - 3 - 4 - 5 - 6 2013-01-01
7 - 8 - 9 - 10 - 11 - 12 2013-01-02
When I mentioned difference I may want to do some some math on those 6 values, but I've done that before.
I tried Julian H's suggestion, and get an empty set as the result. Not sure why, I'll play with that some more.
Thanks
ASKER
Thanks for everyone's help, I think I've almost got it.
regmigrant & Julian -- I used your concept and have it outputting exactly what I wanted in terms of structure.
The only problem I have now is if one of the data points doesn't exist for a given timestamp, then no row is returned for that timestamp at all. Does anyone have any suggestions. My query has grown quite a bit in complexity since I got it working, but if I use either of yours, I have the same issue.
regmigrant & Julian -- I used your concept and have it outputting exactly what I wanted in terms of structure.
The only problem I have now is if one of the data points doesn't exist for a given timestamp, then no row is returned for that timestamp at all. Does anyone have any suggestions. My query has grown quite a bit in complexity since I got it working, but if I use either of yours, I have the same issue.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
Are your Devices numbered 1 - 6, the Int column contains that ID and that the volumes for each of the six machines are timestamped exactly the same so at every update you get six records?
You are asking for 'difference of two volumes in the same row' -are you comparing the volumes from different machines or from the same machine at different times?
How often are the rows updated?
In you output you quote 6 volumes and a timestamp, are these 6 different machines or 6 differences over a time period
In general joining a table to itself is exactly the same as joining two tables:-
Select a.id, a.volume, b.id, b.volume, a.timestamp
from devicetable a, devicetable b
where a.id = b.id
However without understanding the points above this will simply give you a result running to the square of the number of rows with duplicated vaues