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 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply