In SQL Server, I have 2 tables: CREATE TABLE Assets (AssetID varchar(25) PRIMARY

Question

In SQL Server, I have 2 tables:

CREATE TABLE Assets
(AssetID varchar(25) PRIMARY KEY NOT NULL,
Title varchar (100) NOT NULL,
AuthorLName varchar(25) NOT NULL,
AuthorFName varchar(25) NULL,
GenreID varchar(25) NOT NULL,
ISBN varchar(25) NOT NULL,
Availability varchar NULL );

Create Table Genre

GenreID varchar(25) NOT NULL PRIMARY KEY,

GenreName varchar(25) NOT NULL;

My question is I am trying to create a query that will return the title of the asset by genre. I am getting stuck.

Select Title, GenreID, GenreName

From Assets Join Genre

On Genre.GenreID = Assets.GenreID

Order By GenreName ASC

Is it because GenreID in the Assets Table is not a foreign key?

Explanation / Answer

Yes the problem is with the Foreign key.

If you have to join 2 tables then the left table should have the primary key as the foreign key in the right table.

CREATE TABLE Assets
(AssetID varchar(25) PRIMARY KEY NOT NULL,
Title varchar (100) NOT NULL,
AuthorLName varchar(25) NOT NULL,
AuthorFName varchar(25) NULL,
GenreID varchar(25) NOT NULL,
ISBN varchar(25) NOT NULL,
Availability varchar NULL,

FOREIGN KEY (GenreID) REFERENCES Genre(GenreID));

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.