MySQL Forums
Forum List  »  General

Filtering without eliminating
Posted by: Aron Beal
Date: April 06, 2012 11:46AM

Hello the forum,

I'm trying to find the most efficient means of constructing a particular query. I'm going to attempt to construct a simplified example of the problem. Given the following tables (and assume appropriate data types and indices):

users: userid, user
tag_associations: user_id, tag_id
tags: tagid, tag

Say I want to filter a list of users to a given tag 'foo', but still want to retrieve all other tags assigned to that particular user. Currently, I'm doing this in a two step process, where the first step might be:

SELECT userid from users
LEFT JOIN tag_associations on (userid = user_id)
LEFT JOIN tags on (tagid = tag_id)
WHERE tag = 'foo';

Now, I can select again, dumping these ids in a temporary table, or using in my server side language of choice, to get the rest of the tags assigned for any given user.

My question is then: is there a way to do this in one step without using a sub-select statement? Limit only to users assigned tag 'foo', but still show all tags for that user? I say 'without a sub-select statement' as the join conditions I'm *actually* using are making that cost-prohibitive, and my version of mysql does not allow limits within sub-select statements.

Options: ReplyQuote


Subject
Written By
Posted
Filtering without eliminating
April 06, 2012 11:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.