Solved

MySQL Inner Join on Same Table

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

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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

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 56

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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

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. …
It is possible to boost certain documents at query time in Solr. Query time boosting can be a powerful resource for finding the most relevant and "best" content. Of course the more information you index, the more fields you will be able to use for y…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

756 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