Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Databases
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss 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 .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old October 27th, 2004, 04:12 AM
Authorized User
 
Join Date: Aug 2004
Location: , , .
Posts: 58
Thanks: 0
Thanked 0 Times in 0 Posts
Default Qry to retrieve from 2 tables and display

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


Reply With Quote
  #2 (permalink)  
Old October 27th, 2004, 06:26 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 0 Times in 0 Posts
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
Reply With Quote
  #3 (permalink)  
Old October 27th, 2004, 07:46 PM
Authorized User
 
Join Date: Aug 2004
Location: , , .
Posts: 58
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #4 (permalink)  
Old October 29th, 2004, 02:03 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Retrieve and Display Image asters .NET Framework 1.x 0 September 18th, 2008 03:56 AM
Retrieve records from 2 tables snufse SQL Server 2000 7 January 29th, 2008 03:31 AM
retrieve and display selected results babywind BOOK: Beginning PHP4/PHP 5 ISBN: 978-0-7645-4364-7; v5 ISBN: 978-0-7645-5783-5 0 February 11th, 2005 04:23 AM
data retrieve from three tables mateenmohd SQL Server 2000 2 October 7th, 2003 04:26 AM
Prb: COUNT qry result NOT SAME as SELECT qry savoym SQL Language 5 July 2nd, 2003 04:44 PM



All times are GMT -4. The time now is 09:14 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.