[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Backup & Restore 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 Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

649 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