Sign in to follow this  

help in writing a difficult sql query

This topic is 3664 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hello, I want to write a query where i can select records by applying 'where' over the records rather than the fields in the table. U can understand what I am trying to say by an example. Let say we have a table named test as follows, id | tid | tag 1 1 a 2 1 b 3 2 a 4 2 b 5 3 b 6 4 a 7 5 a 8 5 b Now i want to select 'tid' value from the records that contains only 'a' and not 'b'. so in this situation it will only return the record where tid=4 because that is the only record that contains only 'a' and not 'b'. I tried my best but i couldn't come up with any query that can do this. If u know of any query that can perform this task i will appreciate a lot. Please help me in this regard. Thank you bye.

Share this post


Link to post
Share on other sites
id | tid | tag

1 1 a
2 1 b
3 2 a
4 2 b
5 3 b
6 4 a
7 5 a
8 5 b

if you want where is equal to 'a', and not 'b', why do you only want where tID = 4.... shouldn't you want

id = 1
id = 3
id = 6
id = 7

these are where tag = 'a'... unless i am missing something

Share this post


Link to post
Share on other sites
Quote:
Original post by ToohrVyk
SELECT tid 
FROM my_table
WHERE tid NOT IN (SELECT tid
FROM my_table
WHERE tag = 'b')

Uhm, that's exactly the same as: SELECT tid FROM my_table WHERE tag <> 'b'

Having said that, I'm not really sure what the OP is after here.
Ah, I now see what you're trying to do:

SELECT tid FROM test WHERE
tag = 'a' AND tid NOT IN (SELECT tid FROM test WHERE tag = 'b')

[Edited by - WanMaster on December 3, 2007 6:32:01 AM]

Share this post


Link to post
Share on other sites
Quote:
Original post by WanMaster
Quote:
Original post by ToohrVyk
SELECT tid 
FROM my_table
WHERE tid NOT IN (SELECT tid
FROM my_table
WHERE tag = 'b')

Uhm, that's exactly the same as: SELECT tid FROM my_table WHERE tag <> 'b'


It's only "exactly the same" if tid is unique, which isn't the case here. For instance, in the example:

tid tag
1 a
1 b


My snippet would return no value, while yours would return 1 (as selected from the first row).

Share this post


Link to post
Share on other sites
Quote:
Original post by ToohrVyk
It's only "exactly the same"

Yeah, I agree it's not exactly the same. I guess read it wrong because it doesn't filter for the 'a' tags.

Quote:
My snippet would return no value, while yours would return 1 (as selected from the first row).


Mine wouldn't either: it collects all tid's with a-tags, then excludes the the tid's for where a tid/b-tag pair exists in the table.

Share this post


Link to post
Share on other sites
Yuk, subqueries. It should be possible and more efficient to rewrite this as a join:

SELECT id,tid from test AS test1 WHERE test1.tag='a'
EXCEPT
SELECT id,tid from test AS test1 WHERE test1.tag<>'a';

Note that you can't pull 'tag' back as a column because then the 2 sets won't match at all, and therefore nothing will be excluded and you'll just get the entire data set as a result.

Some versions of SQL might have MINUS instead of EXCEPT. Others may not implement either, so you'd use the traditional LEFT JOIN hack, something resembling this:

SELECT id,tid FROM test AS test1
LEFT JOIN test AS test2
ON (test1.tag <> test2.tag)
WHERE test1.tag = 'a' AND test2.tag = NULL;

Share this post


Link to post
Share on other sites
Quote:
Original post by Kylotan
Yuk, subqueries. It should be possible and more efficient to rewrite this as a join:

SELECT id,tid from test AS test1 WHERE test1.tag='a'
EXCEPT
SELECT id,tid from test AS test1 WHERE test1.tag<>'a';

You would need to remove the id from the column list in order to make it work though. Currently it makes all the rows unique and no records would be excluded from the first statement's result.

Quote:
SELECT id,tid FROM test AS test1
LEFT JOIN test AS test2
ON (test1.tag <> test2.tag)
WHERE test1.tag = 'a' AND test2.tag = NULL;

I'm not a big fan of subqueries either, but I'm not sure that a join will make things easier or faster here.

Share this post


Link to post
Share on other sites
Quote:
Original post by WanMaster
You would need to remove the id from the column list in order to make it work though. Currently it makes all the rows unique and no records would be excluded from the first statement's result.


Correct, my mistake.

Quote:
I'm not a big fan of subqueries either, but I'm not sure that a join will make things easier or faster here.


A subquery is more readable than the left join hack, but joins are almost always the more efficient way of retrieving the data, since they form the intrinsic algebra upon which relational databases are built. If at all possible, I'd always recommend people attempt to understand joins and use them appropriately instead of resorting to subqueries which are pretty much the 'nested loop' of data retrieval.

Share this post


Link to post
Share on other sites

This topic is 3664 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this