mysql query please help 1

[libby02]

I have created a database for a college assessment, the assessment asks for a database od a CD collection and therefore information to be held on Discs, Artists and Songs and that at least one artist must have more than one disc in the database…………it states that I must be able to extract a list of all discs and songs by an artist who has more than one CD in the database.To this end I created three tables…………discs artists and songs……..each has a unique id primary key field and each has the other`s primary keys as foreign keys where appropriate, ie; the songs table has a songsid field a discid field an artistid field and songtitle field……the discs table has a discid field an artistid field and disctitle field the artists table has simply an artistid and artistname field…I need to know how to extract the a list of CD`s and songs by the one artist who has more than one CD in the database from the discs and songs tables…….so as I inserted an artist with two discs I am looking to extract the names of the two discs and the names of all the songs held on these two discs in one list by doing one query, sincere apologies if this appears as clear as mud , I have tried to give all details necessary but will give additional info if required. Tables creation syntax is as below :-


songs table

CREATE TABLE songs (
songid INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (songid),
discid INT,
artistid INT,
songtitle VARCHAR (60));

discs table

CREATE TABLE discs (
discid INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (discid),
cost DECIMAL (8,2),
disctitle VARCHAR (60),
releasedate DATE );

artists table

CREATE TABLE artists (
artistid INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (artistid),
artistname VARCHAR (60) );


Actual assessment question as below:-

You are required to design a database to store information about a music CD collection, Comprehensive information must be stored about Artists, CD`s and Songs.
You must fully document your design clearly showing all details of all tables, fields, field types, field sizes ,Primary and foreign keys, NOT NULL fields and all relationships in the database and populate with appropriate information. Use MySQL to implement your design, at least two of the CD`s must have been recorded by the same artist.
You must be able to extract the following information:
1. A list of all CD`s released before 1995.
2. A list of all CD`s and songs by one of the artists who has more than one CD in the collection.
3. The total cost of all the CD`s in the collection.

 

[KarveR]

Pointers only, I'm not doing your work, just helping ok

You only need 2 tables, artist and album.

table person (this will help the query later) should contain a primary key(unique),possibly the name of the artist(how many groups do you know with identical names) call it id, title of album, release date and price.

table album should contain a key to link to person table primary key(same key perhaps ) - call it id also,track number, title, any other stuff.

then you query the tables together:
select * from a.album,p.person where a.id = p.id and a.artist = 'Pink Floyd';

this would list all tracks in the album database belonging to Pink Floyd, in any order.

Add to the end of the query: Order by a.album;
and hey presto a list of all tracks ordered by album.

hope you are getting the idea by now, more pointers if required later.

KISS -> Keep It Small n Simple

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[libby02]

many thanks for replying, I`ll have a bash at it, absolutely understand about not doing it for me........believe me I have put in some work at this.........and can`t understand why I cant pull out the data required from the tables I created..thanks again..Elizabeth

 

[KarveR]

doing queries on 3 tables gets real complicated real quick, selecting from 2 is so straightforward, and really thats the easiest way to do what you want anyway. ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[libby02]

yes I can imagine, however I only want to extract data from two tables......the discs table (incidentally in error I omitted in my table creation syntax that the discs table includes artstid field also, sorry) and the songs table...apologies if I`m confusing you, it`s just that I`ve got the database built as described above, if it is not possible to query it in it`s current form I will try your design, many thanks for taking the time to help.....

 

[sleipnir214]

Karver,

I'm going to have to disagree with you. But what else is new, right?


libby02,

I recommend that you stick with the three-table schema.

Without the "songs" table, the only place to store song lists for an album are in the "albums" table, as multiple columns or a single text field or something equally ugly.

Also, though I haven't looked at the problem long enough to prove it, I'd bet a small sum that a three-table schema will make answering question 2 a lot easier. ______________________________________________________________________
TANSTAAFL!

 

[lemysql]

You definitely should keep the 3 tables you have and not reduce them to 2 tables.

You can get all the data you need by using either of the queries below. They both do the same thing; just different ways of going about it.

SELECT a.artistname, d.cost, d.disctitle, d.releasedate, s.songtitle
FROM (artists AS a INNER JOIN discs AS d ON a.artistid = d.artistid) INNER JOIN songs AS s ON d.discid = s.discid

SELECT a.artistname, d.cost, d.disctitle, d.releasedate, s.songtitle
FROM artists AS a, discs AS d, songs AS s
WHERE d.artistid = a.artistid
AND s.discid = d.discid

 

[libby02]

many thanks for all the help,....I persevered and came up with SELECT DISTINCT songs.songtitle,discs.disctitle FROM songs,discs WHERE discs.discid = songs.discid AND songs.artistid=1; and wey hey it works......

 

[KarveR]

Woot gratz and only 2 tables I notice .. easy innit.

//Karv ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[libby02]

yes this particular query extracted from only two tables but the database consisted of three in total.....and incidentally, it works without the DISTINCT............