Qry to retrieve from 2 tables and display

tsimsha · October 27th, 2004, 04:12 AM

Hi,

I have two tables.
table1
--------
spindex(PK autonumber) job_code layno yd wd

table2
--------
subindex(PK Autonumber) spindex(FK) rollno batchno weight color

I want to retrieve data from 2 tables by giving job_code and layno.
I used this query.

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

It works fine when both tables have data.
If table1 has data and table2 dont have related data, its not retrieving any data. I want to show data from table1 eventhough table2 dont have data. What could be the query?

Finally i want to display in HTML table. Table1 data in header section and table2 data in body section.

Thanx in advance

happygv · October 27th, 2004, 06:26 AM

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

tsimsha · October 27th, 2004, 07:46 PM

Yeah vijay,

Initially there should be related record. In case of edit/delete,
user may delete all the records in the table2. Then we have only in table1. The constraint b/w 2 tables is oncascade update only. If you want to delete table1 data, we must delete table2 data also. If we delete all records in table2 then only i can delete table1 data.
Your sollution is ok but i am using Access. If that is the case i have to open two recordsets. Cant i use only one recordset?

Thanx

happygv · October 29th, 2004, 02:03 AM

No, You cannot do that with single recordset in this case, as the related info is ***non-existing***. But for me this doesn't seem to be logical. Why would one need to get a resultset which actually doesn't exist. When there is no related records, how can I ask it to return data related to it which is not there, that too using a join. May be if you can explain what exactly you are trying to do, it would be better to understand what it is actually required and we would be in a position to help you out.

Cheers!

_________________________
- Vijay G
Strive for Perfection