MySQL Forums
Forum List  »  Other Migration

Re: Subtracting tables
Posted by: Natasha Hurley-Walker
Date: August 11, 2004 03:47AM

It's the same query really, I just hoped not to drag in all the details as it's a bit complicated!

To reiterate as clearly as possible, I have:

A table of 20 known periods

ID | Period
-------------------
A | 3.456
B | 2.987
etc ...

A table of 8 harmonics
Harmonic | Multiplier
--------------------------------------
1/4 | 0.25
3 | 3
etc...

A table of around 40000 results

Some info columns | Period
------------------------------------------------------
blah blah blah | 3.4560500782
more blah blah | 1.49350361
and a bit more | 4.5223678
etc ...

I want to match the periods from the results table with (known period multiplied by each harmonic). I want to display

some info columns | Period | ID | Harmonic
-------------------------------------------------------------------
blah blah blah | 3.4560500782 | A | 1
more blah blah | 1.49350361 | B | 1/2
and a bit more | 4.5223678 | |

The first two rows match known periods but the last one does not.

I have had some success: I can display just the matches quite easily using the 'WHERE ID IS NOT NULL' as you suggested. However I have trouble displaying more rows without also displaying all the harmonics that SQL multiplies the results by, e.g.:

some info columns | Period | ID | Harmonic
-------------------------------------------------------------------
blah blah blah | 3.4560500782 | A | 1
blah blah blah | 3.4560500782 | | 1/2
blah blah blah | 3.4560500782 | | 1/3
blah blah blah | 3.4560500782 | | 2
---
and a bit more | 4.5223678 | | 1
and a bit more | 4.5223678 | | 1/2
and a bit more | 4.5223678 | | 1/3
and a bit more | 4.5223678 | | 2
etc...

Not displaying the harmonics column and using DISTINCTROW produces:

some info columns | Period | ID
-------------------------------------------------------------------
blah blah blah | 3.4560500782 | A
blah blah blah | 3.4560500782 |
---
and a bit more | 4.5223678 |

Which is the best I've come up with so far :(

Also, I'm running 3.23 and am unlikely to be able to upgrade. Otherwise I was thinking subqueries might be useful...

Thanks for listening! :)

Tash

Options: ReplyQuote


Subject
Views
Written By
Posted
10449
August 10, 2004 08:25AM
4106
August 10, 2004 02:33PM
3368
August 10, 2004 02:34PM
Re: Subtracting tables
3882
August 11, 2004 03:47AM
3105
August 11, 2004 03:52AM
3143
August 11, 2004 04:08AM
2835
August 11, 2004 10:20AM
3016
August 12, 2004 07:26AM
2885
August 12, 2004 07:31AM
2589
August 12, 2004 07:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.