Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MySQL Inner Join on Same Table

Posted on 2014-02-20
8
1,081 Views
Last Modified: 2014-04-23
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!
0
Comment
Question by:compsol1993
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 19

Expert Comment

by:regmigrant
ID: 39874658
I can get a sense of what you want but need more information:-

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
0
 
LVL 55

Expert Comment

by:Julian Hansen
ID: 39875145
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;

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 39877479
Sample data from your table and the desired results would help us understand your intent.
0
Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

 

Author Comment

by:compsol1993
ID: 39894960
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
0
 

Author Comment

by:compsol1993
ID: 39895919
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.
0
 
LVL 55

Accepted Solution

by:
Julian Hansen earned 400 total points
ID: 39896067
This should fix my earlier post. As for including the rows that have one missing.
Either you will need to use script or a stored procedure.
Or you will need to run some sort of preprocess to add the missing values with a volume of 0.

SELECT v1.vol, v2.vol, v3.vol, v4.vol, v5.vol, v6.vol, v1.`timestamp` FROM
v AS v1, v AS v2, v AS v3, v AS v4, v AS v5, v AS v6 
WHERE v1.id = 300001 AND v2.id = 300002 AND v3.id = 300003 AND 
      v4.id = 300004 AND v5.id = 300005 AND v6.id = 300006
AND v1.timestamp = v2.`timestamp` AND  v1.`timestamp`=v2.`timestamp` AND v1.`timestamp`=v3.`timestamp` AND 
    v1.`timestamp`=v4.`timestamp` AND v1.`timestamp` = v5.`timestamp` AND v1.`timestamp` = v6.`timestamp`;

Open in new window

0
 
LVL 19

Assisted Solution

by:regmigrant
regmigrant earned 100 total points
ID: 39896084
if you want to get a return even when some items are null and you can't predict which ones are going to be missing you will need to a full join - which I am not sure MySQL supports directly. - On oracle you add 'FULL JOIN' before the where clause - if MySQL can't handle it you will need to decide how to simulate the same functionality.

There are various ways to simulate it but the most common one (if your DB support it) is with a union between the set you have now (using left join) with a duplicated set using right join and excluding any that appear in both left and right joins
select ID from Device as a
   left outer join device as b on a.id = b.id
union all
select * from device as a
   right outer join device as b on a.id = b.id

where a.id is null

Open in new window

I can see this being quite complex for you six joins but here's an article that may help:
http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/

Alternatively - if MySQL support NVL and the missing items are null (rather than 0) you could use that to substitute a dummy value in the join
0
 

Author Closing Comment

by:compsol1993
ID: 40017726
Thanks!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In Solr 4.0 it is possible to atomically (or partially) update individual fields in a document. This article will show the operations possible for atomic updating as well as setting up your Solr instance to be able to perform the actions. One major …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question