complex query

[Sasi]

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

[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

[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

[Sasi]

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

[Sasi]

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