Solved

MySQL Inner Join on Same Table

Posted on 2014-02-20
8
1,128 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
[X]
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
  • 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 58

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 58

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

628 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