Query Query

Originally I thought this was going to be a DISTINCT issue, but I don't
think that is the case.
I have 3 tables..
Apps
ID AppName
1 ApplicationOne
Tests
ID TestCase
1 Run Overnight
TestAttributes
ID AppID TestID Type OS
ProjectID
1 1 1 Stress Windows XP
1
2 1 1 Quick Check Windows XP 1
3 1 1 Larger Check Windows XP 1
If I do..
SELECT DISTINCT Tests.TestCase, Apps.AppName
FROM Tests
LEFT OUTER JOIN Tests ON TestAttributes.TestID = Tests.ID
LEFT OUTER JOIN Apps ON TestCases.AppID = Apps.ID
WHERE (TestAttributes.ProjectID = '1')
ORDER BY TestCase
I will get
Run Overnight ApplicationOne
This is what i need to display in a grid, however I will need to know all 3
IDs for the 3 seperate types.
Is it possible in a query to get something like
Run Overnight ApplicationOne
1
2
3
Where the 1,2,3 would be the 3 IDs from the TestAttributes table.
With more data it would be seomthing like
Run Overnight ApplicationOne
1
2
3
Run Daytime ApplicaitonTwo
4
5
Run Whenever ApplicationThree
6
7
8
9
Basically I want to display one distinct test case, but still get all the
IDs for the no distinct ones. I could obviously do this client side, but
the performance may not be the best.Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
There is no such thig as a "magical universal" id number in an RDBMS.
Did you mean something like this?
CREATE TABLE Applications
(app_id INTEGER NOT NULL PRIMARY KEY,
app_name CHAR(20) NOT NULL);
In a relational model, an entity is the sum of its attributes. So why
did you split the attributes into another table? That violates rules
about mixing data and metadata
Why did you use multiple names for the same data element
(TestCases.AppID = Apps.ID)? See how vague that makes the sentence " I
will need to know all 3 IDs for the 3 separate types." Which id do you
mean? How can you build a data dictioanry with this mess?
CREATE TABLE Tests
(test_nbr INTEGER NOT NULL PRIMARY KEY,
app_id INTEGER NOT NULL
REFERENCES Applications(app_id),
test_type CHAR(10) NOT NULL,
os CHAR(10) NOT NULL,
project_id INTEGER NOT NULL);
Ther is no table named TestCases, so your sample query makees no sense.
Can we try again?|||In a stored procedure, you can browse your tests case with a CURSOR and for
each one store several lines in a temporary table :
- the test case & app.name
- one line for each test attribute id
Build the temp table by an IDENTITY column, and you'll can do a
select ... order by Id_field.
JN.
"Lucas Graf" <lgraf2000@.comcast.net> a crit dans le message de news:
OFSIfSCIFHA.3428@.TK2MSFTNGP10.phx.gbl...
> Originally I thought this was going to be a DISTINCT issue, but I don't
> think that is the case.
> I have 3 tables..
> Apps
> ID AppName
> 1 ApplicationOne
> Tests
> ID TestCase
> 1 Run Overnight
> TestAttributes
> ID AppID TestID Type OS ProjectID
> 1 1 1 Stress Windows XP 1
> 2 1 1 Quick Check Windows XP 1
> 3 1 1 Larger Check Windows XP
> 1
> If I do..
> SELECT DISTINCT Tests.TestCase, Apps.AppName
> FROM Tests
> LEFT OUTER JOIN Tests ON TestAttributes.TestID = Tests.ID
> LEFT OUTER JOIN Apps ON TestCases.AppID = Apps.ID
> WHERE (TestAttributes.ProjectID = '1')
> ORDER BY TestCase
> I will get
> Run Overnight ApplicationOne
> This is what i need to display in a grid, however I will need to know all
> 3 IDs for the 3 seperate types.
> Is it possible in a query to get something like
> Run Overnight ApplicationOne
> 1
> 2
> 3
> Where the 1,2,3 would be the 3 IDs from the TestAttributes table.
> With more data it would be seomthing like
> Run Overnight ApplicationOne
> 1
> 2
> 3
> Run Daytime ApplicaitonTwo
> 4
> 5
> Run Whenever ApplicationThree
> 6
> 7
> 8
> 9
> Basically I want to display one distinct test case, but still get all the
> IDs for the no distinct ones. I could obviously do this client side, but
> the performance may not be the best.
>|||More clear depiction of what I am asking.
CREATE TABLE Apps(
app_id smallint
IDENTITY(1,1)
PRIMARY KEY,
app_name varchar(50) NOT NULL
)
CREATE TABLE Tests(
test_id smallint
IDENTITY(1,1)
PRIMARY KEY,
testcase varchar(50) NOT NULL
)
CREATE TABLE TestAttributes(
attribute_id smallint
IDENTITY(1,1)
PRIMARY KEY,
attr_appid smallint NOT NULL,
attr_testid smallint NOT NULL,
attr_type varchar(50) NOT NULL,
attr_OS varchar(50) NOT NULL,
attr_projid smallint NOT NULL
)
go
INSERT Apps values('Application One')
INSERT Tests values('Run Overnight')
INSERT TestAttributes values('1','1',' Stress','Windows XP','1')
INSERT TestAttributes values('1','1','Quick Check','Windows XP','1')
INSERT TestAttributes values('1','1','Longer Check','Windows XP','1')
Using this query
SELECT DISTINCT Tests.testcase, Apps.app_name
FROM TestAttributes
LEFT OUTER JOIN Tests ON TestAttributes.attr_testid = Tests.test_id
LEFT OUTER JOIN Apps ON TestAttributes.attr_appid = Apps.app_id
WHERE (TestAttributes.attr_projid = '1')
ORDER BY TestCase
Gives me
testcase app_name
Run Overnight Application One
But i will also need to know all 3 of the attribute_id's that correspond to
that test case
Using this query
SELECT DISTINCT Tests.testcase, Apps.app_name,attribute_id
FROM TestAttributes
LEFT OUTER JOIN Tests ON TestAttributes.attr_testid = Tests.test_id
LEFT OUTER JOIN Apps ON TestAttributes.attr_appid = Apps.app_id
WHERE (TestAttributes.attr_projid = '1')
ORDER BY TestCase
I get
testcase app_name attribute_id
Run Overnight Application One 1
Run Overnight Application One 2
Run Overnight Application One 3
I don't want to show alll 3, however I need to know all 3 attribute_id's
So ideallly would like a query to give me something like
testcase app_name
Run Overnight Application One
attribute_id
1
attribute_id
2
attribute_id
3