[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sql 7.0 Syntax help

Posted on 2005-05-02
3
Medium Priority
?
249 Views
Last Modified: 2010-03-19
Hello experts:

I need some help comparing the contents of two tables.  I have two tables, tbl_InvoiceTable and tbl_InvoiceTable_Batch_Headers_Sub.  Here are the field definitions of each:

tbl_InvoiceTable
-------------------
Examnumber
ControlID
CustomerID
DateStamp
Status

tbl_InvoiceTable_Batch_Headers_Sub
------------------------------------------
Payment_ID
BatchID
Examnumber
Datestamp
OrgAmount

Now, I need to run a query that will pull the unpaid invoices from tbl_InvoiceTable from 06/30/2003 to 12/31/2003 and see if these unpaid invoices are in tbl_Invoicetable_Batch_Headers_Sub.  The common field between the two tables is ExamNumber [which is the invoicenumber].  If the Examnumber is in tbl_Invoicetable_Batch_Headers_Sub, then I need to pull the datestamp, if not, I need to display a blank date.

Is this possible?  Any help will be appreciated.

0
Comment
Question by:zdpl0a
  • 2
3 Comments
 
LVL 51

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 13909275
SELECT a.*, b.Datestamp FROM tbl_InvoiceTable a LEFT JOIN tbl_InvoiceTable_Batch_Headers_Sub b ON a.Examnumber = b.Examnumber WHERE a.Status = "Unpaid"
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13909287
This will include all records from tbl_InvoiceTable in which [Status] is "Unpaid".  If there is a matching record (based on [Examnumber]) in the second table, that record's [Datestamp] will be pulled also.  Otherwise, it will be a NULL value.
0
 

Author Comment

by:zdpl0a
ID: 13909758
Thank you routinet ... this gave me the solution with a minor tweak ...


Thanks again
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question