Somewhat stuck with just 1 last MYSQL command

Can you actually post the question you have to answer too? band_members is completely irrelevent going by what you've said so far, so I assume you either haven't explained the question properly (again :p) or there are several parts to the question? Telescopi's answer makes a reasonable assumption about the question (no single band CDs), but it would be nice to actually see a verbatim copy of what you are answering to stop us all guessing :P
 
Beepcake said:
Can you actually post the question you have to answer too? band_members is completely irrelevent going by what you've said so far, so I assume you either haven't explained the question properly (again :p) or there are several parts to the question? Telescopi's answer makes a reasonable assumption about the question (no single band CDs), but it would be nice to actually see a verbatim copy of what you are answering to stop us all guessing :P
Click to expand...

I can't help but getting the feeling we're doing your home work for you... I'm all for helping, so post up your ideas and efforts so far and we'll point you in the right direction. If we just do it for you, you'll never learn :)

Or ask your tutor for help / see you lecture notes :)
 
Can scan the paper if you like beepcake, my tutor is helpful when they get the time, there on holiday most of the time now and get back to me when they get time, so only had 1 set of 'feedback' so far, so its sparse and any knowledge on the forums would be great.

boycee - its a workshop paper (i.e. a mySQL question sheet with tasks and questions on), homework was all in by June 5th ;) :p

Iv done most of it, its just the last part, ill stick up a coherent explanation of the questions and my answers shortly.

Any pointers is much appreciated so far.
 
Right hopefully this will make it easier to understand, a shot of the database layout:

c3a1ea89b2.gif


The origonal question:

For each cd on which bands have collaborated, list the cd_id and number of bands involved. Collaboration means that in relation releases there are two or more band_ids associated with one cd_id value. Hint: you need a GROUP BY and a HAVING clause.
Click to expand...

Makeh anyy more senseh ? :confused::D

But since, iv found out that the previous question to that:

For those bands that have had a number 1 hit, list the name of the band and the total number of their records that made it to number 1. Hint: You need to use GROUP BY to get groups of band names and you need a COUNT(*) in the SELECT part of the statement
Click to expand...

I got wrong, so any clues? lol
 
SELECT cd_id, count(band_id)
FROM releases
having count(band_id) >1

SELECT b.band_name, count(cds.*)
FROM bands b
INNER JOIN releases r
ON r.band_id = b.band_id
INNER JOIN cds
ON r.cd_id = cds.cd_id
WHERE cds.position = 1
GROUP BY b.band_name
 
Hi simon, many thanks for your feedback, tried those commands no go unfort, tried this for the origonal question:

SELECT cd_id, count(band_id)FROM releases having count(band_id) > 1 group by band_id;

Unfort kicks out a syntax 1064 error, it looks logically corrrect, struggling to see why it still not works ?

EDIT: Doesnt seem to like the group by clause


THIS WORKED:

SELECT cd_id, count(band_id)FROM releases GROUP BY band_id having count(band_id) > 1;

'cd001', 20
'cd011', 22
'cd024', 58
'cd037', 2
'cd045', 4
'cd051', 28
'cd053', 12
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom