CODEWITHSUNDEEP
mysqlsql
Toms
Toms

Reputation: 13

SQL select records using id

I have a table called Entertainment. The columns in Entertainment include PrimaryKeys from different tables

The columns in Entertainment are:

EntertainmentID|AgentID|GenreID

I have another table Named "Band" it is one of the subgroups from Entertainment ( the other one isn't necessary)

I want a query that outputs: The Agents details & The Band Name where the Genre = rock

I'm not sure how to do this

The Columns in Agent are

AgentID|AgentName|AgentMobile

The columns in Genre are

GenreID|GenreName

The columns in Band are

EntertainmentID|BandName

Here is what I've got so far:

SELECT Concat(a.AgentFName,' ', a.AgentLName) AS 'Agent Name', a.AgentMobile, a.AgentEmail, b.BandName
FROM Agent a 
Join Band b on a.AgentID = b.EntertainmentID
WHERE Genre IN
(SELECT GenreName
FROM Genre
WHERE Genre='rock');

I got an error message that said GenreName is an unknown column

Upvotes: 0

Views: 38

Answers (1)

Ryan Wilson
Ryan Wilson

Reputation: 10765

Here you go:

SELECT Concat(a.AgentFName,' ', a.AgentLName) AS 'Agent Name', a.AgentMobile, a.AgentEmail, b.BandName
FROM Agent a
--Join Entertainment on Agent AS Entertainment holds a foreign key to Agent
JOIN Entertainment e on e.AgentId = a.AgentId 
--Join Band on Entertainment as Band holds a foreign key to Entertainment
Join Band b on b.EntertainmentId = e.EntertainmentId
--Finally join Genre on Entertainment as Entertainment holds a foreign key to Genre
JOIN Genre g on g.GenreId = e.GenreId
WHERE g.GenreName = 'rock' --Filter down to only rows where GenreName is 'rock

Upvotes: 1

Related Questions