Classic ASP DatabasesDiscuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
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.
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
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?
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
Qry to retrieve from 2 tables and display
Linear Mode
