SQL SubQueries syntax

Question

I am working on a problem that involves a sub select query. I have the databases set up in order to get the select query to work, but I'm relatively new to these subqueries and am a little lost. I need return band name and album titles of said bands with more than 1 album. Here is my code so far.

SELECT b.Name, a.Title
FROM Band as b, Album as a
WHERE (SELECT * 
       FROM Album as al
       WHERE al.BID = a.BID)
GROUP BY b.Name
HAVING Count(a.BID) > 1;

Any help will be greatly appreciated.

Why don't people read the documentation? It is explained in there! — arkascha
– arkascha, Commented Apr 9, 2014 at 23:56

dima · Accepted Answer · 2014-04-10 00:22:09Z

1

try this

SELECT b.Name, a.Title 
FROM Band b 
INNER JOIN Album a ON b.BID=a.BID
WHERE a.BID IN (SELECT BID FROM Album GROUP BY BID HAVING COUNT(BID)>1)

This will work given that Album table has a BID as a foreign key for Band table

edited Apr 10, 2014 at 0:22

answered Apr 10, 2014 at 0:00

dima

1,1891 gold badge9 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user3420788 Over a year ago

Seeing as I created the tables and the data within them, I know that it should return 1 band, and that band's 3 albums...It's doing neithe. Even with this code.

dima Over a year ago

share the schema for these 2 tables (what columns they have), this will help in answering the question

user3420788 Over a year ago

Your code returned all the bands and their album, I'm trying to find only the bands that have more than 1 album.

dima Over a year ago

sorry, misunderstood your question, thought you'd need to return all bands that have at least 1 album... here, I edited my answer, take a look

dima Over a year ago

No problem, glad it helped! Also, if the answer helped you please get into habit of accepting answers otherwise people will not be helping you anymore if they see you're not doing it...

Collectives™ on Stack Overflow

SQL SubQueries syntax

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related