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

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.

-------------Problems Reply------------

SELECT contentID
FROM tags
WHERE tagID in (334, 338, 342)
GROUP BY contentID

--In general
SELECT contentID
FROM tags
WHERE tagID in (...) --taglist
GROUP BY contentID
HAVING COUNT(DISTINCT tagID) = ... --tagcount

Here's a solution that has worked much faster than the for me on a very large database of objects and tags. This is an example for a three-tag intersection. It just chains many joins on the object-tag table (objtags) to indicate the same object and stipulates the tag IDs in the WHERE clause:

SELECT w0.objid

FROM objtags t0
INNER JOIN objtags t1 ON t1.objid=t0.objid
INNER JOIN objtags t2 ON t2.objid=t1.objid

WHERE t0.tagid=512
AND t1.tagid=256
AND t2.tagid=128

I have no idea why this runs faster. It was inspired by the search code in the MusicBrainz server. Doing this in Postgres, I usually get a ~8-10x speedup over the HAVING COUNT(...) solution.

The only alternative way i can think of is:

select a.contentid from tags a
inner join tags b on a.contentid = b.contentid and b.tagid=334
inner join tags c on a.contentid = c.contentid and c.tagid=342
where a.tagid=338

I don't know if this is better but it might be more maintainable

select contentid from tags where tagid = 334
select contentid from tags where tagid = 338
select contentid from tags where tagid = 342

You'd have to build it dynamically which wouldn't be as bad as your original solution.

What type of SQL? MS SQL Server, Oracle, MySQL?

In SQL Server doesn't this equate to:

select contentid from tags where tagid IN (334,338,342)

Category:sql Views:0 Time:2008-10-02
Tags: sql join

Related post

  • Django custom SQL returning single row of results when query returns 2? 2010-04-17

    I have a custom SQL call that is returning different results to the template than I get when I run the same query against the database directly, 1 row vs 2 Query - copied from Django Debug Toolbar: SELECT ((Sum(new_recruit_interviews) / Sum(opportuni

  • Select all row for specific column query SQL Server 2008 2010-08-17

    i want to know what is the wrong in this query I want to select all rows for 1 column string command = "select money from User_Data"; SqlCommand update_money = new SqlCommand(command, con_string.con); SqlDataReader money_reader; money_reader = update

  • SQL Query execution time fast, but fetching rows is slow 2011-04-09

    This is a two part question, but first some background information: I have a TSQL query in Sybase that reports an execution time of 0.328 seconds, however its taking around 20-30 seconds to retrieve approximately 5000 rows. The query has two subquery

  • Can I force a T-SQL query with "Group By" to return rows which have no data? 2011-05-25

    Sorry if this title of this question isn't any good; I'm not sure I can succinctly describe it, so here's the verbose version: I'm trying to generate a report, and I'm actually not sure if what I want to do can be entirely done within a SQL statement

  • Query problem - rows are returned when query is run in sql navigator , but not in my c# program 2011-09-09

    Update: This is the query from the debugger, which was retrieved from a string builder: {SELECT * FROM FCR.V_REPORT WHERE DATE BETWEEN to_date('14/09/2001' , 'dd/mm/yyyy') AND to_date('30/09/2011' , 'dd/mm/yyyy')} If you remove the curly brackets and

  • SQL Server Stored Procedure - Use Row Count in Select query 2012-02-26

    My stored procedure (SQL Server 2005) returns a dataset where one field depends, among other things, on the number of rows returned by the query. I can make a simplified first query that allows me to get @@ROWCOUNT but, in that case, the procedure re

  • Create two or more triggers in a row by executing a query with SQL Server 2015-03-17

    Which is the right syntax in order to create two or more triggers in a row by executing a query with SQL Server? I tried the following one: GO CREATE TRIGGER TRG_MyTable_UpdatedAt ON dbo.MyTable AFTER UPDATE AS UPDATE dbo.MyTable SET UpdatedAt = GETD

  • select top 5 rows returned by particular query sql server 2010-07-08

    I want to retrieve top 5 rows returned by this query. How to do this select COUNT(trippackageID), trippackageid from tbl_holiday_booking group by trippackageID --------------Solutions------------- You are not specifying the order, do you want the pac

  • Number of rows considered by a query 2011-09-06

    Is there a way to find out how many rows are considered by a query without performing a count(*) on the source? example: Create table test (col1 int , col2 int) Insert into test select 1,2 union select 2,3 Select avg(col1) from test If I use @@rowsaf

  • SQL Update woes in MS Access - Operation must use an updateable query 2009-02-11

    I have a select query which does some text manipulation to essentially reformat a field so that I can look it up in another table: If my first table if I have a field like "J1/2" it looks up the ID of a record in a different table with J1 and J2 in t

  • How can I count the numbers of rows that a mysql query returned? 2009-03-03

    How can I count the numbers of rows that a mysql query returned? using PHP.. --------------Solutions------------- Getting total rows in a query result... You could just iterate the result and count them. You don't say what language or client library

  • Default row ordering for select query in oracle 2009-05-22

    In Oracle, what is the the default ordering of rows for a select query if no "order by" clause is specified. Is it the order in which the rows were inserted there is no default ordering at all none of the above. --------------Solutions------------- A

  • MySQL Need help constructing query: join multiple tables into single row 2010-03-20

    Forgive me if this question has been asked and answered, I've searched and found a few that look similar but I'm too much of a novice with SQL to adapt them to my needs. Also forgive me if I don't use the correct terminology, I know it can be annoyin

  • Does the speed of the query depend on the number of rows in the table? 2010-03-29

    Let's say I have this query: select * from table1 r where r.x = 5 Does the speed of this query depend on the number of rows that are present in table1? --------------Solutions------------- The are many factors on the speed of a query, one of which ca

  • Please will you help tune a 7-table-join mysql count query where tables contain 30,000+ rows? 2010-06-30

    I have an sql query that counts the number of results for a complex query. The actual select query is very fast when limiting to 20 results, but the count version takes about 4.5 seconds on my current tables after lots of optimizing. If I remove the

  • FMDB SQLite question: row count of a query? 2010-10-01

    does anyone know how to return the count of a query when using FMDB? If I executeQuery @"select count(*) from sometable were..." I get an empty FMResultSet back. How can I get the row count of the query? Do I need to do a query like "select * from so

  • SQL UPDATE statement - returns success but 0 rows affected 2010-12-29

    I am sending an SQL UPDATE query, and the mySQL database is behaving strangely. It returns a successful response, but 0 rows are affected. The code is: UPDATE place SET city='Qatar' AND country='Qatar' WHERE placeid='25' Response: Successful 0 row(s)

  • sql server 2008 management studio not checking the syntax of my query 2011-01-04

    As always, there will be a reasonable explanation for my surprise, but till then.... I have this query delete from Photo where hs_id in (select hs_id from HotelSupplier where id = 142) which executes just fine (later i found out that the entire photo

  • MDX equivalent to SQL's GROUP BY for excluding duplicate rows 2011-04-01

    My cube has a fact table that contains a row for every modification made to any entity. What I need is a measure that will return a count of the entities based on the selected dimensions. So if the user selects a month from the date dimension then th

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.119 (s). 11 q(s)