Solved

MySQL Inner Join on Same Table

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

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 31

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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 51

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 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

20 Experts available now in Live!

Get 1:1 Help Now