CODEWITHSUNDEEP
mysql
user2948897
user2948897

Reputation: 169

HAVING, JOINS, GROUP BY MySQL

I formulated the following query:

How to gather the names of all titles which contain at least nine tracks.

I thought that I could use a select statement having and group by and an inner join in the following way:

select ti.title from titles ti
inner join tracks tr on (ti.titleid = tr.titleid)
where tr.tracknum having count >=9;

The MySQL Query Engine is throwing me an error: Unknown column 'tr.tracknum' in 'having clause'. Is there way I can revise my query to produce a result?

Upvotes: 0

Views: 73

Answers (2)

anycard
anycard

Reputation: 322

select ti.title 
from titles ti
inner join tracks tr on ti.titleid = tr.titleid
group by ti.title
having count(*) >=9;

Upvotes: 2

Mihai
Mihai

Reputation: 26784

select ti.title from titles ti
inner join tracks tr on (ti.titleid = tr.titleid) 
GROUP BY title 
having count(title) >=9;

HAVING refers to columns in the GROUP BY.

Upvotes: 3

Related Questions