CODEWITHSUNDEEP
sqloracle-database
user1603200
user1603200

Reputation: 65

having count with join

I want to find out which playlist have more than 2 songs. The statement works but I want the name of the playlist and the count() for the songs displayed. I think i have to use a join but I didn't get it how this should work. Can someone helps please? 

playlist table
++++++++++++++
id
name

playlist_songs table
++++++++++++++++++++
song_id
playlist_id


SELECT p.name FROM playlist p 
WHERE p.id in (SELECT s.playlist_id counter FROM playlist_songs s
group by playlist_id
having count(song_id)>2);

Upvotes: 3

Views: 9764

Answers (5)

vidyadhar
vidyadhar

Reputation: 3168

always use join if possible 

SELECT p.name play_list,count(s.song_id) total_songs 
FROM playlist p 
INNER JOIN playlist_songs s 
ON p.id = s.playlist_id 
GROUP BY p.name 
HAVING COUNT(s.song_id) >= 2;

Upvotes: 0

TechDo
TechDo

Reputation: 18629

Please try:

SELECT a.name
FROM
  playlist a INNER JOIN  playlist_songs b 
  ON a.id = b.playlist_id
GROUP BY a.name
HAVING COUNT(*) >1

Upvotes: 0

gaborsch
gaborsch

Reputation: 15748

This is what you need:

SELECT p.name as name, count(*) as counter
FROM playlist p left outer join playlist_songs s on (p.id = s.playlist_id)
GROUP BY p.name
HAVING count(*) > 2

Upvotes: 0

Paul Alan Taylor
Paul Alan Taylor

Reputation: 10680

Try:-

SELECT
  p.name,
  count(s.song_id)
FROM
  playlist p
INNER JOIN
  playlist_songs s
ON
  p.id = s.playlist_id
GROUP BY
  p.name
HAVING
  COUNT(s.song_id) >= 2

This is SQL server syntax, but should work on Oracle.

Upvotes: 2

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

I want the name of the playlist and the count() for the songs displayed.

This one of the advantages of using JOIN over the IN predicate:

SELECT 
  p.name, 
  COUNT(song_id) counter
FROM playlist p 
INNER JOIN playlist_songs s ON  p.id = s.playlist_id 
GROUP BY playlist_id
HAVING COUNT(song_id) > 2;

Upvotes: 4

Related Questions