We help IT Professionals succeed at work.
Get Started

SQL Query using mulitple dates

Last Modified: 2012-08-16

I have 2 tables, one holds user details and the other table holds dates that are available for the users. Im wanting to return all users where there available date matchs a users date. For example:

User 1 is available on the 1/1/2012, 2/1/2012, 3/1/2012 and 4/01/2012
User 2 is available on the 2/1/2012
User 3 is available on the 2/1/2012
User 4 is available on the 3/01/2012 and 4/01/2012

I would like to return all users that match User 1 available dates (in this case, User 2, User 3 and User 4 would be returned along with there dates). I started with this query but its just returning 1 row (User 4), when i know theres more than one match

DECLARE @MyDate date
DECLARE @Id varchar

SELECT @MyDate = AvailableDate From AvailableDates where Id in (1) --this is User 1
SET @Id = 1

FROM UserDetails
JOIN AvailableDates
ON UserDetails.Id = AvailableDates.Id  
WHERE AvailableDate = @MyDate and AvailableDates.Id <> @Id --dont need User 1 details to be returned

Can anyone help on this conundrum?
Watch Question
This problem has been solved!
Unlock 2 Answers and 8 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE