CODEWITHSUNDEEP
sqlselectinsert
Aaron Lyall
Aaron Lyall

Reputation: 41

SQL SELECT FROM TWO TABLES INNER JOIN

I am trying select from my Album and ArtistBand Table and display the Name and Bandname in alphabetical order. When in try to use the select statement below it shows up with all the albums on each band. Any help would be appreciated project for school.

CREATE TABLE ArtistBand 
(
    ArtistID INT AUTO_INCREMENT PRIMARY KEY,
    BandName VARCHAR(255) NOT NULL,
    NameOfMembers VARCHAR(255),
    NumberOfMembers INT
);

CREATE TABLE Genre
(
    GenreID INT AUTO_INCREMENT PRIMARY KEY,
    GenreType VARCHAR(255) NOT NULL
);

CREATE TABLE Album 
(
    AlbumID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR (255) NOT NULL,
    ReleaseDate DATE NOT NULL,
    Producers VARCHAR (255),
    ArtistID INT,
    GenreID INT,
    FOREIGN KEY(ArtistID) REFERENCES ArtistBand(ArtistID),
    FOREIGN KEY (GenreID) REFERENCES Genre (GenreID)
);

CREATE TABLE LookUp
(
    AlbumID INT,
    GenreID INT,
    FOREIGN KEY (AlbumID) REFERENCES Album (AlbumID),
    FOREIGN KEY (GenreID) REFERENCES Genre (GenreID)
);

INSERT INTO ArtistBand 
VALUES
    (NULL,'ACDC','BrianJohnson',4),
    (NULL,'Metallica','JamesHeitfield',4),
    (NULL,'TheBeatles','JohnLennon',4),
    (NULL,'JayZ','ShawnCarter',1),
    (NULL,'DangerMouse','BrianBurton',1);

INSERT INTO Album 
VALUES
    (NULL,'BackInBlack','1980-01-01',NULL,NULL,NULL),
    (NULL,'TheBlackAlbum','1991-01-01',NULL,NULL,NULL),
    (NULL,'WhiteAlbum','1968-01-01',NULL,NULL,NULL),
    (NULL,'TheBlackAlbum','2003-01-01',NULL,NULL,NULL),
    (NULL,'TheGreyAlbum','2004-01-01',NULL,NULL,NULL);

INSERT INTO Genre 
VALUES
    (NULL,'HeavyMetal'),
    (NULL,'HeavyMetal'),
    (NULL,'Pop'),
    (NULL,'HipHop'),
    (NULL,'HipHop');

SELECT   
    ArtistBand.BandName, Album.Name
FROM     
    ArtistBand
JOIN     
    Album 
ORDER BY 
    ArtistBand.BandName;

Upvotes: 2

Views: 67

Answers (1)

rcs
rcs

Reputation: 7227

You need to add condition on your join, that the ArtistID should be the same in both tables. So your query will look like below:

SELECT   ArtistBand.BandName, Album.Name
FROM     ArtistBand
JOIN     Album ON ArtistBand.ArtistID = Album.ArtistID
ORDER BY ArtistBand.BandName;

Upvotes: 3

Related Questions