CODEWITHSUNDEEP
mysql
smitthy
smitthy

Reputation: 289

Get all albums by a specific band in MySQL

I've created a few tables called Formed, Track, Album, Band, Customers in MySQL and I'm using MySQL Workbench. In the Formed table, I have the year a band was formed, country and city the band was formed, and the band members. In the Track table, I have the songs, the track count so how many songs are on an album, the title of the album, and the total track duration. In the Album table, I have the price, title, and genre. In the Band table, I have the record label, and in the Customers table, I have the name, phone number, email, and if they've paid.

However I want to try and create an SQL script that can get all the albums by a band. The only way that I've seen is by using 

SELECT Albums,Band FROM Album;

Or

SELECT * FROM Album;

But I'm not 100% sure if there's a better, more effective way of doing it.

EDIT

The code that I have is:

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS Formed;
DROP TABLE IF EXISTS Album;
DROP TABLE IF EXISTS Band;
DROP TABLE IF EXISTS Customers;
DROP TABLE IF EXISTS Track;

SET FOREIGN_KEY_CHECKS=1;

CREATE TABLE Formed(
    FormedID int AUTO_INCREMENT, 
    YearFormed int, 
    CountryFormed varchar(50), 
    CityFormed varchar(50), 
    BandMembers varchar(400), 
    PRIMARY KEY(FormedID))
    ENGINE=InnoDB;

CREATE TABLE Track (
    TrackID int AUTO_INCREMENT, 
    AlbumID int,
    Songs varchar(200), 
    TrackNumber varchar(20), 
    Title varchar(30), 
    TrackDuration varchar(6), 
    PRIMARY KEY(TrackID)) 
    ENGINE=InnoDB;

CREATE TABLE Album(
    AlbumID int AUTO_INCREMENT,
    TrackID int,
    BandID int,
    KEY(TrackID),
    KEY(BandID),
    Price int, 
    PublicationDate varchar(11), 
    Title varchar(30), 
    Genre varchar(36),
    PRIMARY KEY(AlbumID))
    ENGINE=InnoDB;

CREATE TABLE Band(
    BandID int AUTO_INCREMENT, 
    AlbumID int, 
    KEY(AlbumID),
    RecordLabel varchar(50), 
    PRIMARY KEY(BandID))
    ENGINE=InnoDB;

CREATE TABLE Customers (
    CustomerID int AUTO_INCREMENT, 
    CName varchar(20), 
    CPhone varchar(12), 
    CEmail varchar(50), 
    CPPaid varchar(50), 
    CPDate date, 
    PRIMARY KEY (CustomerID))
    ENGINE=InnoDB;

ALTER TABLE Track 
ADD FOREIGN KEY (AlbumID) REFERENCES Album(AlbumID)ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE Album
ADD FOREIGN KEY (TrackID) REFERENCES Track(TrackID)ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE Album
ADD FOREIGN KEY (BandID) REFERENCES Band(BandID)ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE Band
ADD FOREIGN KEY (AlbumID) REFERENCES Album(AlbumID)ON DELETE SET NULL ON UPDATE CASCADE;

Upvotes: 2

Views: 623

Answers (1)

dcreight
dcreight

Reputation: 641

Use a join, so for instance if you wanted the title for a particular band:

Select a.Title from Album a
Inner Join Band b on a.BandID = b.BandID
Where a.BandID = X

Upvotes: 2

Related Questions