CODEWITHSUNDEEP
sqlms-access
Jeff Brady
Jeff Brady

Reputation: 1498

How to see if a field entry has a corresponding entry in another field?

I have a table named Table1 that looks like this:

Company    Start1      Start2      Start3      End1      End2      End3
-----------------------------------------------------------------------------
010105     1/2/1990                            7/30/2001
023123     2/1/1998    2/1/2012                5/15/2008 
124557     1/15/2001   
436567     1/12/2004   1/12/2010   1/12/2012   1/15/2005 1/15/2010 
034534     1/1/2002    1/1/2003    1/1/2004    5/1/2002  5/1/2006  5/1/2004
123456     1/1/2002    1/1/2003                          5/1/2006

I'm looking for any Company that has a Start1 without a corresponding End1, or a Start2 without a corresponding End2, etc. The entries in the fields won't be exact matches, but if one has an entry the other should also.

Is this possible?

The query in the above example would return the following:

023123 (has Start2 but no End2)
124557 (has Start1 but no End1)
436567 (has Start3 but no End3)
123456 (has Start1 but no End1)

(I only need the company numbers, not the wording in parenthesis)

Thanks!!

Upvotes: 1

Views: 61

Answers (3)

DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

SELECT Company FROM yourtable
  WHERE Start1 IS NOT NULL AND End1 IS NULL OR
  Start2 IS NOT NULL and End2 IS NULL OR
  Start3 IS NOT NULL and End3 IS NULL

Upvotes: 1

PM 77-1
PM 77-1

Reputation: 13334

SELECT t.Company FROM Table1
WHERE t.start1 IS NOT NULL and t.end1 IS NULL or
      t.start2 IS NOT NULL and t.end2 IS NULL or
      t.start3 IS NOT NULL and t.end3 IS NULL

If the above does not solve your problem please provide more information.

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270607

Assuming there are no additional columns besides the 3 pairs listed, this can be done with a simple WHERE clause that tests for a non-NULL start date in each column along with a corresponding NULL end. If any of the three conditions is met, the Company will be returned.

SELECT DISTINCT Company
FROM Table1
WHERE
  (Start1 IS NOT NULL AND End1 IS NULL)
  OR (Start2 IS NOT NULL AND End2 IS NULL)
  OR (Start3 IS NOT NULL AND End3 IS NULL)

If your empty fields are actually empty strings '' instead of NULL, substitute the empty string as in:

(Start1 <> '' AND End1 = '')

Note, the DISTINCT isn't needed if the Company column is a unique or primary key.

Upvotes: 2

Related Questions