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

Posted on 2012-03-28
Medium Priority
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.

Question by:cdemott33
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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


Author Comment

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

Accepted Solution

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)
LVL 75

Expert Comment

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

764 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