Wrox Programmer Forums
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #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


  #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 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
  #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

  #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 1 Time in 1 Post
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


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 04: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 05: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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.