Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language 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 August 8th, 2005, 12:13 PM
Registered User
 
Join Date: May 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Getting info from two tables

Hello;

I have two tables

Table 1 Rx
-------
RxNumber, RxId(no duplicates), RxDate, Name, Notes, Type, DOB

Table 2 Email
-------
EmailId, RxId(duplicates ok), Subject, Message, To, From, Type


I would like the result to be

Result Table
------------
Rx Id, RxDate, Name, Notes(or Subject), Type

Table 1 can be searched by date, or Name or DOB I want the result of this search to go throug table 2
and return to me all emails that match thre previous results RxIds

For example Table 1
RxNumber, RxId, RxDate, Name, Notes, Type, DOB
1 99 1/1/2005 Person1 Note1 1 1/1/95
2 98 3/1/2005 Person2 Note2 1 1/1/96
3 97 5/1/2004 Person3 Note3 1 1/1/97
4 96 1/1/2004 Person4 Note4 1 1/1/98
5 95 6/1/2005 Person5 Note5 1 1/1/99

For Example Table 2
EmailId, RxId(duplicates ok), Subject, Message, To, From, Type
1 100 S1 M1 T1 F1 2
2 98 S2 M2 T2 F2 2
3 101 S3 M3 T3 F3 2
4 95 S4 M4 T4 F4 2
5 98 S5 M5 T5 F5 2
6 95 S6 M6 T6 F6 2
7 96 S7 M7 T7 F7 2
8 98 S8 M8 T8 F8 2
9 100 S9 M9 T9 F9 2
10 100 S10 M10 T10 F10 2

If I do a search for Rxs that were prescribed from 1/1/2005 to 7/1/2005
I would like the resulting table to be
RxId RxDate Name Note/Subject Type
99 1/1/2005 Person1 Note1 1
98 3/1/2005 Person1 Note2 1
98 3/1/2005 Person2 S2 2
98 3/1/2005 Person2 S5 2
98 3/1/2005 Person2 S8 2
95 6/1/2005 Person5 Note5 1
95 6/1/2005 Person2 S4 2
95 6/1/2005 Person2 S6 2

I have tried a combination of inner joins and Union with no luck any suggestions

Thanks

Fernb200



  #2 (permalink)  
Old August 9th, 2005, 11:20 PM
Friend of Wrox
Points: 4,332, Level: 27
Points: 4,332, Level: 27 Points: 4,332, Level: 27 Points: 4,332, Level: 27
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2003
Location: , NJ, USA.
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Use a Left Outer Join from Table1 to Table2

Select t1.RxID, t1.RxDate, t1.Name, t1.Notes, t1.Type
From Table1 t1
   Left Outer Join Table2 t2 ON t1.Rxid = t2.RxID
Where ....




Similar Threads
Thread Thread Starter Forum Replies Last Post
creating tables within tables in access??? carswelljr Access 3 August 23rd, 2006 01:21 PM
Need Info mr_shoaib SQL Server 2000 1 October 17th, 2005 07:02 AM
Need Info mr_shoaib VB.NET 2002/2003 Basics 1 October 13th, 2005 05:18 AM
Pull info from 2 tables stacy Classic ASP Databases 6 March 19th, 2004 10:46 AM
Where would I get info on how to download info aidanc ASP.NET 1.x and 2.0 Application Design 1 September 29th, 2003 02:45 PM





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