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

Posted on 2012-03-28
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 500 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

724 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