CODEWITHSUNDEEP
mysqlsql
user2948897
user2948897

Reputation: 169

SQL Subquery using HAVING COUNT

My question is: How do I show the name of each artist which has recorded at least one title at a studio where the band: "The Bullets" have recorded?

I have formulated the following query:

select ar.artistname, ti.titleid from artists ar
where exists
(
select 0
from titles ti where ti.artistid = ar.artistid
and exists
(
select 0
from studios s
where s.studioid = ti.studioid
and ar.artistname = 'The Bullets'
)
);

However, I need to include HAVING COUNT(ti.titleid) > 0 to satisfy this part, "each artist which has recorded at least one title" in the question.

I also am unsure as to how to match the artistname, "The Bullets" who have recorded at least one studio.

The Artists table resmebles the following:

Artists
-------
ArtistID, ArtistName, City

The Tracks table resmebles the following:

Tracks
------
TitleID, ArtistID, StudioID

The Studios table resmebles the folllowing:

Studios
-------
StudioID, StudioName, Address

I also must specify that I cannot use joins, e.g., a performance preference.

Upvotes: 1

Views: 6072

Answers (3)

wvdz
wvdz

Reputation: 16651

These two requirements are quite silly - using HAVING COUNT(*) > 0 and no joins. I've never heard of choosing sub-queries over joins to enhance performance.

However, I think this query fulfills these requirements.

SELECT a.ArtistName FROM Artist a
WHERE EXISTS
(
    SELECT t1.ArtistId FROM Track t1
    WHERE t1.ArtistId = a.ArtistId
    AND EXISTS
    (
         SELECT * FROM Track t2
         WHERE t1.StudioId = t2.StudioID
         AND t2.ArtistName = 'The Bullets'
    )
    GROUP BY t1.ArtistId, t1.StudioId
    HAVING COUNT(*) > 0
);

Upvotes: 1

Hogan
Hogan

Reputation: 70523

The studio(s) where the bullets recorded

SELECT StudioID
FROM Sudios S
JOIN Tracks T ON S.StudioID = S.StudioID
JOIN Artists A ON T.ArtistID = A.ArtistID AND A.ArtistName = 'The Bullets'

Every Artist who recorded there

SELECT A1.ArtistName, A1.City
FROM Artist A1
JOIN Tracks T1 ON T1.ArtistID = A2.ArtistID
WHERE T1.SudioID IN 
(
  SELECT StudioID
  FROM Sudios S
  JOIN Tracks T ON S.StudioID = S.StudioID
  JOIN Artists A ON T.ArtistID = A.ArtistID AND A.ArtistName = 'The Bullets'
) T

Upvotes: 2

Lajos Veres
Lajos Veres

Reputation: 13725

Maybe like this?

select ArtistName from Artists where ArtistID in (
  select ArtistID from Tracks where StudioID in (
    select StudioID from Tracks where ArtistID in (
      select ArtistId from Artists where ArtistName='The Bullets'
    )
  )
)

I don't see why do you think having is needed.

Upvotes: 4

Related Questions