Sql : left join exclude values based on another table

Question

I am trying to figure out this without any success i have tried going through several posts but cannot come to a solution. Situation is like this : ( There are no foreign key constraints ) Table A ID | VAL Table B ID|VAL|TEMP RESULT REQUIRED
1 | A 1| A | 2 A 1 | B 1| A | 2 C 1 | C 1| B | 1 D 1 | D 1| C | 2 E 1 | E 1| D | 2 F 1 | F 1| G | 6 H 1 | H When i run
SELECT DISTINCT A.VAL,B.TEMP FROM A LEFT JOIN B ON A.VAL=B.VAL AND B.TEMP > 1 This is returning all rows of TABLE A and rows which are common to TABLE B but not validating the condition ( b.val > 1) because since row B is in Table A, it is getting included in the result . However i want that if val b < 2 then it should be not included in result.

Answer 1

If you want to exclude results from the second table, then don't use a left outer join. Just use an inner join:

SELECT DISTINCT A.VAL, B.TEMP
FROM A INNER JOIN
     B 
     ON A.VAL = B.VAL AND B.TEMP > 1;

Or, in your case, you might want B.TEMP < 2.

Answer 2

Add following WHERE clause to your query

WHERE B.Val IS NOT NULL

Answer 3

I think this is what you are looking for:

select distinct a.val
from a
left outer join b on (a.val = b.val)
where 
  (b.temp > 1 or b.val is null)

You want to do the test on B.TEMP after doing the outer join. In the where clause, you are testing for two things. Firstly, you a checking that the value of TEMP is greater than 1. This is the condition set forward in the question. The second condition (b.val is null) covers those rows from table A that don't have a corresponding row in table B. For example, rows 'E', 'F' and 'G' in table A don't match to anything in table B, so all the columns of B will be null after the outer join. you might want to look at this link for more information on outer joins: What is the difference between "INNER JOIN" and "OUTER JOIN"?

Howveer, I have noticed that the rows in table B don't need to be unique. You have two rows where VAL= 'A'. What would happen if the TEMP column had different values where one met the condition and the other didn't.

Another option for getting the result might just be to use NOT IN or NOT EXISTS. An example might be:

select * from a
where a.val not in (
  select val 
  from b 
  where temp < 2
);