Select rows with no match between two tables by sem

Question

I have two tables, Table1 and Table2. I want to select distinct rows in Table2 that are not contained in Table1. Here is an example:

  Table1        

   | A  |   | sem|
   ------------------
1. | aa |   | 1 |
   ---------------
2. | bb |   | 1 |
   ----------------
3. | aa |   | 2 |
   -----------------
4. | cc |   | 2 |
   ---------------

Table2

   | B  |
   ------
1. | aa |
   ------
2. | aa |
   ------
3. | bb |
   ------
4. | cc |
   ------
5. | cc |
   ------
6. | ee |
   ------
7. | ee |
   ------

I would want the those row who is not common in this two table when sem = 1 like output for sem = 1 result

| B |

1. | cc |
2. | ee |

Answer 1

You could try something like this.

SELECT B
FROM Table2 b
WHERE NOT EXISTS (
    SELECT *
    FROM Table1 a
    WHERE a.A = b.B)

From what I can gather, the reason why yours isn't working is because your getting all the values that ARE in both table1 and table2. What you should be doing is like what I did above. Get all the values that are in both tables and check which values in both tables are NOT within the result set, that is why i have set up a sub-query. I'm a little rusty on my SQL so take what i've said with a gain of salt.

Answer 2

You can do this with an outer join and a where clause. In this case, a right outer join, I think:

SELECT cd.cGenotype
FROM  dbo.TestResults tr right outer join
      dbo.CombinedData cd
      ON (tr.TestResult = cd.cGenotype)
where tr.testresult is null
GROUP BY cd.cGenotype