View Single Post
 
Old October 27th, 2004, 06:26 AM
happygv happygv is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

When you dont have related data for JOBCODE and its SPINDEX in table2, obviously, you wont get any data since you are joining both the tables to retrieve related information, but I don't understand how you would expect it to return data when there is not related information in table1. May be this is what you are looking. But the result set would be different in both the cases(when related data exists or not). Though I am not sure if this is what you wanted, you can try this out.
Code:
If (select count(*) from table1,table2 where table1.job_code='04A001' and table1.layno=1 and table2.spindex=table1.spindex)>0
BEGIN
    select job_code,layno,yd,wd,rollno,batchno,weight,color from table1,table2 where table1.job_code='04A001' and table1.layno=1 and table2.spindex=table1.spindex
END
ELSE
BEGIN
    select job_code,layno,yd,wd from table1 where job_code='04A001'
END
Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection