July 26, 2006 at 3:34 am
I am having difficulty in creating a view from two tables.
I have a set of batches that require a set of tests. Each batch type will have the same tests.
I have a table to store the results with each unique test having a record with the test index, result index and the result.
This can be linked back to the test list.
So, I have a table showing ALL tests required for a batch and a table showing results for the tests.
The problem I am having is showing a full list of tests for a specific batch under certain conditions.
I can show the list if NO tests have been done and a list if ALL tests have been done, but if a batch type has already got some tests, a new batch of the same type does not show because the resulting query shows all performed tests, it does not return the required tests if an existing batch type has had the test performed...???
example
Batch1_xx has performed test 1 and 2 and I can view these
Batch1_xy has performed test 1 but NOT 2: I can only see test 1 in the result. I expect to also see test 2 but the results field s are empty because the test has not been done.
After all this waffeling I think I can sumerise by: I want to return all tests for a batch regardless of whether the test has been done or not. If it hasnt I want the test fields to be empty, if it has, It want the results displayed.
I am almost there with the SQL but its just this last step...
Can any one help...? Did I explain it well enough...?
July 26, 2006 at 3:47 am
You must use LEFT JOIN here. And possibly a CROSS JOIN, depending on your table designs.
I can't tell you more until you post the table design here, together with some sample data and the expected output, based on the provided sample data.
N 56°04'39.16"
E 12°55'05.25"
July 26, 2006 at 3:57 am
Is it something like this you want?
-- prepare test data
declare @batches table (batchid int primary key clustered)
insert @batches
select 1 union all
select 2 union all
select 3
declare @tests table (testid int primary key clustered)
insert @tests
select 1 union all
select 2 union all
select 3
declare @jobs table (jobid int primary key clustered, batchid int, testid int)
insert @jobs
select 1, 1, 1 union all
select 2, 1, 2 union all
select 3, 2, 1
-- Do the work. This part is the code for the actual View!
select b.batchid,
t.testid,
case when j.testid is null then 'No' else 'Yes' end Done
from @batches b
cross join @tests t
left join @jobs j on j.batchid = b.batchid and j.testid = t.testid
-- End of View...
order by b.batchid,
t.testid
The output from the example above is
BatchID TestID Done
------- ------ ----
1 1 Yes
1 2 Yes
1 3 No
2 1 Yes
2 2 No
2 3 No
3 1 No
3 2 No
3 3 No
Good luck! I hope this helps you and give you an idea of what to do.
N 56°04'39.16"
E 12°55'05.25"
July 26, 2006 at 4:03 am
Thanks for the prompt response Peter.
I was about to post some sample data but the example you have shown is similar to what I want , where you have 'DONE' I will have actual result data from the results table.
I will try your sugestion (it has given me a pointer in the right direction) and let you know how I get on...
July 26, 2006 at 4:14 am
-- prepare test data
declare @batches table (batchid int primary key clustered)
insert @batches
select 1 union all
select 3 union all
select 2
declare @tests table (testid int primary key clustered)
insert @tests
select 1 union all
select 3 union all
select 2
declare @jobs table (jobid int primary key clustered, batchid int, testid int, resultid int)
insert @jobs
select 1, 1, 1, 3 union all
select 2, 1, 2, 1 union all
select 3, 2, 1, 2
declare @results table (resultid int primary key clustered, resultcode varchar(10))
insert @results
select 1, 'Done' union all
select 2, 'Excellent' union all
select 3, 'Trash'
-- Do the work
select b.BatchID,
t.TestID,
case when r.resultid is null then 'No result yet!' else r.resultcode end JobCode
from @batches b
cross join @tests t
left join @jobs j on j.batchid = b.batchid and j.testid = t.testid
left join @results r on j.resultid = r.resultid
BatchID TestID JobCode
------- ------ --------------
1 1 Trash
1 2 Done
1 3 No result yet!
2 1 Excellent
2 2 No result yet!
2 3 No result yet!
3 1 No result yet!
3 2 No result yet!
3 3 No result yet!
N 56°04'39.16"
E 12°55'05.25"
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply