Somewhat stuck with just 1 last MYSQL command

Combat squirrel

Combat squirrel

Combat squirrel

Soldato
Joined
7 Aug 2004
Posts
11,264
Hey all, from a table for example:

'cd001', 1
'cd002', 1
'cd003', 1
'cd004', 1
'cd005', 1
'cd006', 1
'cd007', 1
'cd008', 1
'cd009', 1
'cd010', 1
'cd011', 2
'cd012', 2
'cd013', 2
'cd014', 2
'cd015', 2
'cd016', 2
'cd017', 2
'cd018', 2
'cd019', 2
'cd020', 2
'cd021', 2
'cd024', 3
Click to expand...

Im trying to list the cd_id (cd020 for example) and the number of bands involved (1, 2 or 3 for example), so id end up with the cd number and band 1, shown 14 times or something like that, how would I do that ? any hints? I know I need a group by and having clause in the command line, but for the last 3 hours it evades me!, any pointers would be great


(PS:Usually the PC 'do your own work' brigade jumps in here, its not marked work or anything like that {as term has finished!} its a work shop paper that we can do to brush up on mysql stuff and im doing that! but this one last exercise is evading me)

Iv done this:

SELECT cd_id, COUNT(*) FROM releases GROUP BY band_id;

Results in:

'cd001', 30
'cd011', 33
'cd024', 87
'cd037', 3
'cd045', 6
'cd051', 42
'cd053', 18

Which I guess is counting the cd_id and then saying how many times it appears of each band_id, but I need it to list the cd_id and number of bands involved, any clues ?
 
Im trying to list the cd_id (cd020 for example) and the number of bands involved (1, 2 or 3 for example), so id end up with the cd number and band 1, shown 14 times or something like that
Click to expand...

I've been staring at you post for like 10 minutes and still don't understand :p Can you rephrase the question?

You want to display every cd_id alongside how many bands are involved? Is the "number of bands involved" another column in your table or a column in a completely seperate table or..?

What's your table structure and exactly what are you trying to get from those table(s)?
 
I've sat and looked at your post for a good 5 minutes and i don't understand what you are trying to achieve either, some clarification please?
 
hehe, ahh ok, well er lets try:

For each cd on which the bands have collaborated, list the cd_id and the number of bands involved. Collabertion means in that the relation 'releases' (name of the table), there are two or more band_ids associated with one cd_id value, the only hint I have is I need a GROUP BY and HAVING clause.

Tablename : releases

cd_id | band_id

Data as in example, bit more of it, upto 9 bands,lol

That help ? :S, sorry it has a brain stinger! lol
 
Glad I wasn't the only one that didn't understand it :) It's early.. but if i'm understanding you correctly don't you just need to do :

SELECT cd_id, COUNT(*) FROM releases GROUP BY cd_id;

That will return the cd_id and a count of the number of groups associated with it.
 
Sounds like your table model is a bit confusing..? You should have something like...

tableName CD
{
id (PK)
name
etc....
}

tableName ArtistOrBand
{
id(PK)
name
etc...
}

cdBand
{
cdId(FK)
bandID(FK)
}

Then you could do nicer queries :)
 
Well if that's the case the query he wants has not been listed yet...

He wants something like.

Code: 
SELECT cd.id
     , count(cb.bandID)
FROM CD cd 
JOIN cdBand cb ON cd.id = cb.cdId
GROUP BY cd.id
 
boycee said:
Well if that's the case the query he wants has not been listed yet...

He wants something like.

Code: 
SELECT cd.id
     , count(cb.bandID)
FROM CD cd 
JOIN cdBand cb ON cd.id = cb.cdId
GROUP BY cd.id
Click to expand...

Unless you want to list cds with no bands (which would be a blank cd :)) the cdBans table contains all the fields you need. If you take the join out the code is the same as above. If you want to list cds with no bands you'd need an outer join anyway.
 
Beepcake said:
Unless you want to list cds with no bands (which would be a blank cd :)) the cdBans table contains all the fields you need. If you take the join out the code is the same as above. If you want to list cds with no bands you'd need an outer join anyway.
Click to expand...

Yeah that's true :)

I did it that way as personally I'd want to probably list more information. Ie CD title, year etc... I'd prob join the band as well so I could show the band name tbh too.
 
SimonCHere said:
If you wanted to show more info, then yes, you need the join(s). Otherwise there is no point.

Simon
Click to expand...

You make me laugh, you offer lots of comments and criticism yet no suggestions or advice of your own :p

I and the OP I'm sure can read my above comment confirming that the joins are only required should you wish to retrieve information from the non-join tables.
 
I didn't see the point in re writing beepcakes code as it was the same as I'd write and then you wandered in saying the table model is a bit confusing.
Combat Squirrel hadn't posted any tables, just an extract containing the 2 columns he wanted.

If you care to check out the rest of my posts in this forum, I've tried a bit of photoshopping for someone, written 2 bits of code for other people, helped with VS settings and offered advice on manipulating images programatically. I'm not quite sure where you get the theory that I offer no suggestions or advice of my own.
And I wasn't the only one who didn't understand the question from the first post.

Simon
 
From what you've said so far you have one table called released with fields cd_id and band_id. cd_id is repeated multiple times for as many band_ids that played on it, I'm assuming the same cd_id and band_id combination will not appear more than once.

Having is used for aggregate conditions in grouped queries - so I'm guessing they intend you to only list collaberations and not single band cds?

select cd_id, (count *) as bands FROM releases GROUP BY cd_id having (count *) > 1
 
Please post a sample of the data from the table/tables you have and another sample of what you'd like the query to produce.
 
Thanks for the feedback guys, I asked my tutor again who made the work shop, and they insist a HAVING clause is needed ? Interesting.

The ENTIRE database consists of tables (4):

1) CREATE TABLE cds (cd_id CHAR (6), rdate DATE,position INT, title VARCHAR (40), weeks INT,PRIMARY KEY (cd_id));

2) CREATE TABLE bands (band_id INT, bandname VARCHAR (40), PRIMARY KEY (band_id));

3) CREATE TABLE band_members (band_id INT (40), name VARCHAR (40), dob DATE, country VARCHAR (40), sex CHAR (1), PRIMARY KEY (band_id));

4) CREATE TABLE releases (cd_id INT, band_id VARCHAR (40) PRIMARY KEY (band_id));


They pointed out:

Where are the NOT NULLs?
In table band_members attribute band_id cannot uniquely identify a row – a band can have many band members.
Click to expand...

Also abit further into the database they pointed out after I altered the table:

You appear to be making band_id a foreign key of table bands, this does not make sense. Band_id will only be a foreign key in one of the tables not both. This is possibly why you could not load data? Could have been getting into a tangle.
Click to expand...

Made a woopsey there I think, I guess this would effect the OP situation?

Damn this is driving me up the wall, I cant sleep until it works :D
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom