Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL query: Simulating an "AND" over several rows instead of sub-querying

Tags:

sql

join

Suppose I have a "tags" table with two columns: tagid and contentid. Each row represents a tag assigned to a piece of content. I want a query that will give me the contentid of every piece of content which is tagged with tagids 334, 338, and 342.

The "easy" way to do this would be (pseudocode):

select contentid from tags where tagid = 334 and contentid in (
    select contentid from tags where tagid = 338 and contentid in (
        select contentid from tags where tagid = 342
    )
)

However, my gut tells me that there's a better, faster, more extensible way to do this. For example, what if I needed to find the intersection of 12 tags? This could quickly get horrendous. Any ideas?

EDIT: Turns out that this is also covered in this excellent blog post.

like image 589
Max Cantor Avatar asked Oct 02 '08 18:10

Max Cantor


People also ask

Which is faster CTE or subquery?

Advantage of Using CTE CTE can be more readable: Another advantage of CTE is CTE are more readable than Subqueries. Since CTE can be reusable, you can write less code using CTE than using subquery. Also, people tend to follow the logic and ideas easier in sequence than in a nested fashion.

Is CTE a subquery?

A CTE (common table expression) is a named subquery defined in a WITH clause. You can think of the CTE as a temporary view for use in the statement that defines the CTE. The CTE defines the temporary view's name, an optional list of column names, and a query expression (i.e. a SELECT statement).

Why some queries return the same row more than once?

The Problem The cause of the error is a subquery returning more than one row of information. This error in multi-row returns on the subquery originates from an outer query failing to use appropriate, designated keywords to specify values for comparison in the subquery.

Can you have multiple subqueries in a Select statement?

You can specify up to 16 subqueries within a single SQL statement, and you can specify subqueries within a subquery. Subqueries run from last to first within the main SQL statement in which they appear.


1 Answers

SELECT contentID
FROM tags
WHERE tagID in (334, 338, 342)
GROUP BY contentID
HAVING COUNT(DISTINCT tagID) = 3


--In general
SELECT contentID
FROM tags
WHERE tagID in (...) --taglist
GROUP BY contentID
HAVING COUNT(DISTINCT tagID) = ... --tagcount
like image 96
Amy B Avatar answered Oct 04 '22 22:10

Amy B