help in writing a difficult sql query

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

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 on other sites
sorry i posted this question in game programming forum by mistake. I was posting it in general programming but went to game programming forum by mistake.

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 on other sites
SELECT tid FROM my_table WHERE tid NOT IN (SELECT tid                   FROM my_table                   WHERE tag = 'b')

Share on other sites
SELECT tid
FROM test
WHERE tid IN (SELECT tid FROM test WHERE tag='a')
AND tid NOT IN (SELECT tid FROM test WHERE tag='b');

should do it.

Share on other sites
Quote:
 Original post by ToohrVykSELECT 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 on other sites
Quote:
Original post by WanMaster
Quote:
 Original post by ToohrVykSELECT 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 on other sites
Quote:
 Original post by ToohrVykIt'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 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 on other sites
Quote:
 Original post by KylotanYuk, 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'EXCEPTSELECT 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 test1LEFT JOIN test AS test2ON (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.

1. 1
2. 2
3. 3
Rutin
19
4. 4
khawk
14
5. 5
frob
12

• 9
• 11
• 11
• 23
• 12
• Forum Statistics

• Total Topics
633659
• Total Posts
3013213
×