help in writing a difficult sql query
Author
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.
Author
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.
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
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
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.
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.
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]
Quote:Original post by WanMasterQuote: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 bMy snippet would return no value, while yours would return 1 (as selected from the first row).
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.
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;
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;
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.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
