Multi-table calculations

Hi,

I'm a newbie that's been lurking for a while, and I've searched but haven't been able to find what I'm looking for. Basically I'm running a query that is supposed to calculate the most commonly
occuring values (with some added complications). I'm running MySQL 3.23.


Here are the relevant tables. Relationship: song has many songinfo's

------------------
table song
------------------
songid
artist
song_name

------------------
table songinfo
------------------
songinfoid
songid
chord
chord_certanity
bpm
genre

I would like a query that returns the following:

artist, song_name, CHORD, BPM, GENRE

CHORD:
This returns the most popular chord based on the chord_certanity column.

Example:
D7, 2
Bm, 4
Bm, 4
D7, 2
D7, 2
Cm7,3

The CHORD would then be Bm because it has a total of 8 chord_certanity points.

BPM
This returns the most commonly occuring value in BPM.

GENRE
This returns the most commonly occuring value in GENRE.


I have been able to write separte queries for all 3 that work, but combining them into one has got me stumped. Any helping getting this to work in one query would be greatly appreciated!

Regards

I think it'll have to remain in mulitple queries. No way that I know of to combine things like when, esp. when you're looking for "max(count())" of one column, "max(count())" of another column, "max(sum())" of another column... etc....

Even if you could, it'd probably be simpler to do 3 easy queries than one complex one.

---John Holmes...

Thanks! I'll try to run all three that puts them into a temp table.

Regards

Thanks! I'll try to run all three that puts them into a temp table.

Regards