user2250206 user2250206 - 1 month ago 6
SQL Question

SQL tables join

I am looking for a way to fetch records in a single sql query for the complex joins. I have three tables lets say users, user_projects and appointments. User can be assigned to multiple projects and user can have multiple appointments on different dates. How do I select all the users which is assigned to project1 and project2 and has appointments on date1 and date2? I am using postgres 9.5

Sample Data:

users
table:

id, name
1, Steve
2, Bill
3, Emma


user_projects
table:

id, user_id, project_id
1, 1, 1
2, 2, 1
3, 3, 1
4, 1, 2


appointments
table:

id, user_id, date
1, 1, 2016-10-07
2, 2, 2016-10-07
3, 3, 2016-10-07
4, 1, 2016-11-15
5, 2, 2016-11-15


For this special case lets say I want to find all the users that belongs to project with id 1 and 2 and has appointment fixed on date 2016-10-07 and 2016-11-15. And the expected output is it should only include user with id 1 ie. Steve in this case

Answer
SELECT ID
FROM USERS U
JOIN (
  -- Users in two projects
  SELECT USER_ID 
  FROM USER_PROJECT
  WHERE PROJECT_ID = 1

  INTERSECTION

  SELECT USER_ID 
  FROM USER_PROJECT
  WHERE PROJECT_ID = 2
) UP ON U.ID = UP.USER_ID
JOIN (
  -- user ids that have appointments on two dates:
  SELECT USER_ID
  FROM APPOINTMENT 
  WHERE DATE = '2016-10-07'

  INTERSECTION

  SELECT USER_ID
  FROM APPOINTMENT 
  WHERE DATE = '2016-11-15'
) A ON U.ID = A.USER_ID

Another way to do it that should have the same performance (maybe this seems better because there are less lines?):

SELECT ID
FROM USERS U
JOIN (
  -- Users in two projects
  SELECT USER_ID 
  FROM USER_PROJECT
  WHERE PROJECT_ID IN (1,2)
  GROUP BY USER_ID
  HAVING COUNT(DISTINCT PROJECT_ID) =  2
) UP ON U.ID = UP.USER_ID
JOIN (
  -- user ids that have appointments on two dates:
  SELECT USER_ID
  FROM APPOINTMENT 
  WHERE DATE IN ('2016-10-07','2016-11-15')
  GROUP BY USER_ID
  HAVING COUNT(DISTINCT DATE) =  2
) A ON U.ID = A.USER_ID
Comments