John F John F - 5 months ago 10
SQL Question

Select table rows when value from other table is null

is there a way to select rows using values from another table?

enter image description here

I want to select data on table 1 using table2

enter image description here

Output will be

enter image description here

enter image description here

I have 2 instances in selecting values in table1 that's why I'm trying to use case on this one, I'm not just sure if it is correct.

select
case when table2.s_num is Null and table2.s_div is Null then
(select * from table1 where table1.item_num = table2.i_num)
from table1, table2


Here are my instances:

1. if t2.s_num is null and t2.s_div is null then select * from t1 where t1.item_num = t2.i_num
2. if t2.s_num is null and t2.s_div is not null then select * from t1 where t1.item_num = t2.i_num and t1.store_div = t2.s_div


I'm not that good in sql, any ideas? thanks!

Answer

is there a way to select rows using values from another table?

You should use a join.

First, look at the output of this query:

select *
from table1 t1
join table2 t2 on t2.i_num = t1.item_num

See how the join works? It matches i_num to item_num and returns only rows where there was a match (that's an inner join, the default kind).

You actually have a more complicated join condition for your two "instances", something like this should express it:

select *
from table1 t1
join table2 t2 on t2.i_num = t1.item_num and (t2.s_div is null or t2.s_div = t1.store_div)

You also want to filter to rows where s_num is null, so just add a where:

select *
from table1 t1
join table2 t2 on t2.i_num = t1.item_num and (t2.s_div is null or t2.s_div = t1.store_div)
where t2.s_num is null
Comments