Solved

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

Posted on 2012-03-28
4
319 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 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)
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

730 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