?
Solved

Help writing t-sql statement checking for a match in tables

Posted on 2012-03-28
4
Medium Priority
?
335 Views
Last Modified: 2012-03-28
I have two table.  Table 1 is called eventInformation and table 2 is called onlineOrder.  What I need to do is this.  I need a list of jobNumbers found in the eventInformation table that have a matching jobNumber in the onlineOrder table.  I wrote this...

SELECT     ei.jobNumber
FROM         eventInformation AS ei INNER JOIN
                      onlineOrder AS oo ON ei.jobNumber = oo.jobNumber AND ei.jobNumber = oo.jobNumber
                      AND ei.arrivalDate BETWEEN '01/01/2011' AND '12/31/2011'   

Open in new window


.. but I don't believe it's working.  Can someone tell me how I can fix my code in order for it to give me the results I'm looking for.

Thanks!
0
Comment
Question by:cdemott33
4 Comments
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 37777589
try :

SELECT     ei.jobNumber
FROM         eventInformation AS ei INNER JOIN
                      onlineOrder AS oo ON ei.jobNumber = oo.jobNumber AND ei.jobNumber = oo.jobNumber
                      AND (ei.arrivalDate >= '01/01/2011' AND ei.arrivalDate <= '12/31/2011')   

Open in new window

0
 

Author Comment

by:cdemott33
ID: 37777639
Tried it... but it gives me the same result.  I'm reading about sub-queries.  Would a subquery work in my case?
0
 
LVL 1

Accepted Solution

by:
ICG earned 2000 total points
ID: 37777671
You are correct in your subquery thought...
SELECT     ei.jobNumber
FROM         eventInformation AS ei
WHERE ei.jobNumber IN (SELECT jobNumber FROM onlineOrder)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37778016
>> but I don't believe it's working.  <<
So what about it is not "working"?
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Screencast - Getting to Know the Pipeline
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

840 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