How to write join query in sub query method?

Question

This is the question: What is the most common stackoverflow tag_type? What companies have a tag of that type?

My Solution:

SELECT type, count(tag) AS count
 FROM tag_type
group by type
order by count desc;

Result of this query is:

type count
cloud 31
database 6

And so on but I selected cloud in the type column as mentioned below and got my result which is correct.

SELECT tag_company.tag, company.name, tag_type.type
 FROM company
 -- Join to the tag_company table
 INNER JOIN tag_company 
 ON company.id = tag_company.company_id
 -- Join to the tag_type table
 INNER JOIN tag_type
 ON tag_company.tag = tag_type.tag
 -- Filter to most common type
 WHERE type='cloud';

My Question: I have got my desired result though but I want to eliminate the manual work by simply typing (type =cloud) and looking for dynamic way of solving this query. How can I do that?

And how can this query be solved with sub query method? Please help.

Or how could I combine these steps in a single query by using a subquery in the WHERE clause instead of the value 'cloud'?

• Fiona Daniel: Is this a right platform to ask or I should ask this on stack overflow instead?

Answer 1

I think this question will be fine here, though it could as well have been asked at SO. One nice property of the relational model is that it is closed under relational operations, that is, the result of a query is a new relation. I'll use Common Table Expressions (CTE), but you may as well use a subquery:

WITH type_counts (type, cnt) AS -- type is a bad identifier,
 -- but I'll ignore that
( SELECT type, count(tag) AS count
 FROM tag_type
 group by type
), company_tag_info AS 
( SELECT tag_company.tag, company.name, tag_type.type
 FROM company
 JOIN tag_company 
 ON company.id = tag_company.company_id
 JOIN tag_type
 ON tag_company.tag = tag_type.tag
)
SELECT cti.*
FROM type_counts tc
JOIN company_tag_info cti
 ON cti.type = tc.type

If you just want information related to the top type, you can order by cnt and limit to one:

WITH type_counts (type, cnt) AS -- type is a bad identifier,
 -- but I'll ignore that
( SELECT type, count(tag) AS count
 FROM tag_type
 group by type
), company_tag_info AS 
( SELECT tag_company.tag, company.name, tag_type.type
 FROM company
 JOIN tag_company 
 ON company.id = tag_company.company_id
 JOIN tag_type
 ON tag_company.tag = tag_type.tag
)
SELECT cti.*
FROM type_counts tc
JOIN company_tag_info cti
 ON cti.type = tc.type
ORDER BY tc.cnt DESC
LIMIT 1;

This can be further simplified, but I believe that the above is the most relevant part for your question.