CODEWITHSUNDEEP
sql
user2948897
user2948897

Reputation: 169

Rewriting Multiple JOINS as SUBQUERIES

I need to find the names of all my salespeople that represent a paticular band in my Artists Table.

My Tables resmeble the following:

Salespeople

SalesID, Firstname, Lastname

Artists

ArtistID, ArtistName

Members

MembersID, Firstname, Lastname. SalesID

The MembersID and Artists tables are related by the following table:

xRefArtistsMembers

ArtistID, MemberID

I formulated the following query:

 select sp.firstname, sp.lastname from salespeople sp
 inner join members m on (m.salesid = sp.salesid)
 inner join xrefartistsmembers x on (x.memberid = m.memberid)
 inner join artists a where a.artistname = "The Bullets";

Is their also a way to do this with subqueries?

Upvotes: 0

Views: 91

Answers (2)

Patrick Y
Patrick Y

Reputation: 853

Try this:

select sp.firstname, sp.lastname from salespeople sp
inner join members m on (m.salesid = sp.salesid)
inner join xrefartistsmembers x on (x.memberid = m.memberid)
inner join artists a on a.artistID = x.artistID
where a.artistname = "The Bullets"

Upvotes: 1

Ian Davis
Ian Davis

Reputation: 109

shouldn't your inner join to artists have an on clause then your where clause?

inner join artists a where a.artistname = "The Bullets";

would be

inner join artists a on a.artistid = x.artistid
where artists.artistname = "The Bullets";

Upvotes: 0

Related Questions