469,088 Members | 1,268 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,088 developers. It's quick & easy.

complex query

Hi all,
i am new to sql. Could anyone help me find a solution to this
problem.
I have a table with 3 fields like this,

test_name varchar(25) primary key
test_result varchar(10)
test_level integer primary key

my data looks like
test1,pass,1
test1,fail,2

test2,pass,1
test2,pass,2

test3,pass,1
test3,fail,2

test4,fail,2
test4,fail,3
the assumption is that all the test should have same status (no matter
pass or fail) in all the test_level.
my query should output the "test_name" which has different results in
different test_level.
In the above example data, test1 passed in level 1 and failed in 2
i want to catch this data.

FYI, I am using MySql.
Thanks in advance,
Sasi
Jul 19 '05 #1
4 2616
BDR
Your question is more complex than the query... hmm, not exactly sure
what you're after.
---------------------------------------------------------
SELECT test_name, test_level, test_result FROM test
GROUP BY test_name
will give you:

test1 1 pass
test2 2 pass
test3 1 pass
test4 2 fail
---------------------------------------------------------
SELECT test_name, test_level, test_result FROM test WHERE
test_result='fail' GROUP BY test_name
will give you:

test1 2 fail
test2 2 fail
test3 2 fail
test4 2 fail
---------------------------------------------------------
SELECT test_level, test_name, test_result FROM test WHERE
test_result='fail' GROUP BY test_level
will give you:

2 test2 fail
3 test4 fail
---------------------------------------------------------

Lots of different ways to `catch` data :-) ... The confusing part is
that you said all the test should have the same status no matter pass or
fail. What do you mean `same status`? You want'em all like together or
something or??

..... snip ....
the assumption is that all the test should have same status (no matter
pass or fail) in all the test_level.
my query should output the "test_name" which has different results in
different test_level.
In the above example data, test1 passed in level 1 and failed in 2
i want to catch this data.

FYI, I am using MySql.
Thanks in advance,
Sasi


Jul 19 '05 #2
BDR
Your question is more complex than the query... hmm, not exactly sure
what you're after.
---------------------------------------------------------
SELECT test_name, test_level, test_result FROM test
GROUP BY test_name
will give you:

test1 1 pass
test2 2 pass
test3 1 pass
test4 2 fail
---------------------------------------------------------
SELECT test_name, test_level, test_result FROM test WHERE
test_result='fail' GROUP BY test_name
will give you:

test1 2 fail
test2 2 fail
test3 2 fail
test4 2 fail
---------------------------------------------------------
SELECT test_level, test_name, test_result FROM test WHERE
test_result='fail' GROUP BY test_level
will give you:

2 test2 fail
3 test4 fail
---------------------------------------------------------

Lots of different ways to `catch` data :-) ... The confusing part is
that you said all the test should have the same status no matter pass or
fail. What do you mean `same status`? You want'em all like together or
something or??

..... snip ....
the assumption is that all the test should have same status (no matter
pass or fail) in all the test_level.
my query should output the "test_name" which has different results in
different test_level.
In the above example data, test1 passed in level 1 and failed in 2
i want to catch this data.

FYI, I am using MySql.
Thanks in advance,
Sasi


Jul 19 '05 #3
thank you very much for your reply & sorry for my bad english.
let me put it in this way,

The field "test_name" is not unique, So I can have same test name more
than once in my data,
eg: let say my data looks like this (test_name,test_level,test_result)

test1,1,pass
test1,2,fail

test2,1,pass
test2,2,pass

test3,1,fail
test3,2,fail

in the above example,
"test2" & "test3" have same "test_result" for all "test_level"
but "test1" have different "test_result" on different "test_level" , I
need catch this information.
i.e., i have get the test_name if my "test_result" are different for
different "test_level"

Hope this will be bit clear,
Thanks
Sasi


BDR <jo*@noemail.com> wrote in message news:<3F**************@noemail.com>...
Your question is more complex than the query... hmm, not exactly sure
what you're after.
---------------------------------------------------------
SELECT test_name, test_level, test_result FROM test
GROUP BY test_name
will give you:

test1 1 pass
test2 2 pass
test3 1 pass
test4 2 fail
---------------------------------------------------------
SELECT test_name, test_level, test_result FROM test WHERE
test_result='fail' GROUP BY test_name
will give you:

test1 2 fail
test2 2 fail
test3 2 fail
test4 2 fail
---------------------------------------------------------
SELECT test_level, test_name, test_result FROM test WHERE
test_result='fail' GROUP BY test_level
will give you:

2 test2 fail
3 test4 fail
---------------------------------------------------------

Lots of different ways to `catch` data :-) ... The confusing part is
that you said all the test should have the same status no matter pass or
fail. What do you mean `same status`? You want'em all like together or
something or??

.... snip ....
the assumption is that all the test should have same status (no matter
pass or fail) in all the test_level.
my query should output the "test_name" which has different results in
different test_level.
In the above example data, test1 passed in level 1 and failed in 2
i want to catch this data.

FYI, I am using MySql.
Thanks in advance,
Sasi

Jul 19 '05 #4
thank you very much for your reply & sorry for my bad english.
let me put it in this way,

The field "test_name" is not unique, So I can have same test name more
than once in my data,
eg: let say my data looks like this (test_name,test_level,test_result)

test1,1,pass
test1,2,fail

test2,1,pass
test2,2,pass

test3,1,fail
test3,2,fail

in the above example,
"test2" & "test3" have same "test_result" for all "test_level"
but "test1" have different "test_result" on different "test_level" , I
need catch this information.
i.e., i have get the test_name if my "test_result" are different for
different "test_level"

Hope this will be bit clear,
Thanks
Sasi


BDR <jo*@noemail.com> wrote in message news:<3F**************@noemail.com>...
Your question is more complex than the query... hmm, not exactly sure
what you're after.
---------------------------------------------------------
SELECT test_name, test_level, test_result FROM test
GROUP BY test_name
will give you:

test1 1 pass
test2 2 pass
test3 1 pass
test4 2 fail
---------------------------------------------------------
SELECT test_name, test_level, test_result FROM test WHERE
test_result='fail' GROUP BY test_name
will give you:

test1 2 fail
test2 2 fail
test3 2 fail
test4 2 fail
---------------------------------------------------------
SELECT test_level, test_name, test_result FROM test WHERE
test_result='fail' GROUP BY test_level
will give you:

2 test2 fail
3 test4 fail
---------------------------------------------------------

Lots of different ways to `catch` data :-) ... The confusing part is
that you said all the test should have the same status no matter pass or
fail. What do you mean `same status`? You want'em all like together or
something or??

.... snip ....
the assumption is that all the test should have same status (no matter
pass or fail) in all the test_level.
my query should output the "test_name" which has different results in
different test_level.
In the above example data, test1 passed in level 1 and failed in 2
i want to catch this data.

FYI, I am using MySql.
Thanks in advance,
Sasi

Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Starbuck | last post: by
4 posts views Thread by ED | last post: by
2 posts views Thread by Ben de Vette | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.