0

I have the following table structure:

table: user
user_id | join_date
    101 | '2012-4-13'
    102 |  '2012-6-4'
    103 |        NULL
    104 |        NULL

table: job
job_id | user_id
    20 |      101
    21 |      103

I want to return a single result set of user records that contains the following:

  1. The results of all users that have a match in the job table.
  2. The results of all users where the join_date is null, AND do not have a record in the job table.

Here is the result set of user_id's I would like to see:

user_id
    101 <-- has a job
    103 <-- has a job
    104 <-- never joined, AND also does not have a job

Do I need to do this using Common Table Expressions or can I do this in a subquery?

Please note I've left out actual details for the sake of brevity. This is not a simple case of an inner join.

Here are each of my queries running separately:

THIS IS THE QUERY THAT RETURNS THE PROPER RESULT FOR #1

SELECT DISTINCT u.*   
FROM [user] u, job uj
WHERE u.user_id = uj.user_id

THIS IS THE QUERY THAT RETURNS THE PROPER RESULT FOR #2

SELECT DISTINCT u.*   
FROM [user] u  
FULL JOIN job uj ON u.user_id = uj.user_id   
WHERE u.join_date IS NULL AND uj.user_id IS NULL
Adam Levitt
  • 10,316
  • 26
  • 84
  • 145

2 Answers2

1

To get `101, 103 & 104, try:

    select distinct u.user_id 
    from [user] u LEFT JOIN job j
    on u.user_id = j.user_id
    where (u.join_date is not null and j.job_id is not null)
          or u.join_date is null
Yogendra Singh
  • 33,927
  • 6
  • 63
  • 73
  • Yes, I'm not looking for a simple query in this case, I've omitted much more complicated details for brevity. – Adam Levitt Dec 13 '12 at 21:32
  • @AdamLevitt Then what is the question? When you do `SELECT DISTINCT u.* FROM [user] u INNER JOIN job uj ON u.user_id = uj.user_id WHERE u.join_date IS NULL`, doesn't it(two changes: Inner join and omitted second filter) return what you want? – Yogendra Singh Dec 13 '12 at 21:41
  • The SQL in your answer does not work because user 104 is a user that I DO want to see in the result set. That's the challenge here. It would be trivial if I just want 101 and 103. – Adam Levitt Dec 13 '12 at 21:44
  • @AdamLevitt Then you may want to use a left join with `or` clause. Please refer my updated answer. This should return you `101` (has date and jobs) and `103`, `104`(no date no job) but filter `102`(date but job or no job) . – Yogendra Singh Dec 13 '12 at 21:56
  • First option gives incorrect results, second one won't execute. – Kevin Dec 13 '12 at 22:06
  • @Kevin First one should give 101 and 103. 104(already mentioned in the answer and removed now). **Why the second will not execute?** There was type in column name which is fixed now. – Yogendra Singh Dec 13 '12 at 22:07
  • I get both an ambiguous column name error and and incorrect syntax error. – Kevin Dec 13 '12 at 22:13
  • 1
    @Kevin The alias was missing in select, which I added now and now I know, the entire issue :) I didn't wrap `user` in braces and `user` is a reserved word. It should be all fine now. – Yogendra Singh Dec 13 '12 at 22:18
  • Yes, that does work. I still prefer mine for maintainability reasons as it reads like the description. Your where clause, while logically the same, is not as easy to read to figure out what is being searched for. – Kevin Dec 13 '12 at 22:28
1

This should do it:

select u.user_id from [user] u
left join job j on u.user_id = j.user_id
where j.job_id is not null or (j.job_id is null and u.join_date is null)

Edit: This is logically the same and "simpler" but the first way "reads" like the problem statement:

select u.user_id from [user] u
left join job j on u.user_id = j.user_id
where j.job_id is not null or  u.join_date is null
Kevin
  • 7,162
  • 11
  • 46
  • 70
  • so this query works nicely, but breaks down when I have to select from mulitple tables in the FROM statement while joining them against the user_id in the WHERE statement. Does this make sense? – Adam Levitt Dec 13 '12 at 22:52
  • Sorry, @AdamLevitt but that does not make sense. Are you joining against the user_id in the user table? If you posted more details, I might be able to help you more – Kevin Dec 14 '12 at 02:16
  • Right... I figured that out. Now what if I want to replace that last line in your second answer with a clause that says "include users that have a NON-null join date, but have a null user_id in the job table?" – Adam Levitt Dec 14 '12 at 02:27
  • so you want all users that have joined and don't have a job? Then it would be "where join_date is NOT null and job_id is null" – Kevin Dec 14 '12 at 14:06