CODEWITHSUNDEEP
sqljoin
Ramki
Ramki

Reputation: 381

SQL to join two tables with one where clause on one table

I have two tables as the following

table1
main_id main_val main_sub main_pk
1             A   NULL     3
3             A    1       3

table2

col_id  col_val
1           A

select table1.main_pk from table1 ,table2 WHERE
table1.main_id = table2.col_id
and table1.main_val = table2.col_val
and table1.main_sub = null

Am expecting the above query to select the first row in table 1 as main_sub is null and the other two columns matches. But it does not. I am just learning SQL basics so am not sure where am going wrong. Please help

Upvotes: 2

Views: 972

Answers (2)

yxre
yxre

Reputation: 3704

SQL is a little weird about NULL values. Use the condition 

....
table1.main_sub IS NULL

for testing for a null specifically. The idea is that NULL is supposed to be interpreted as nothing, so nothing -equals- nothing can't ever be true because you can't compare something that doesn't exist.

Upvotes: 2

Carlos Alves Jorge
Carlos Alves Jorge

Reputation: 1985

I didn't fully understand the question but I think this is what you want:

SELECT * from table1 INNER JOIN table2 on table1.main_val=table2.main_val WHERE table1.main_sub IS NULL

If not I think it directs you in the right path

Upvotes: 3

Related Questions