Solved

MySQL Inner Join on Same Table

Posted on 2014-02-20
8
1,063 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 52

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
 

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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 52

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now