CODEWITHSUNDEEP
sqlsql-server
John F
John F

Reputation: 142

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!

Upvotes: 0

Views: 3638

Answers (6)

Blank
Blank

Reputation: 12378

You can use LEFT JOIN:

select
from table1
left join table2
on table2.s_num is null
and (table2.s_div = table1.store_div or table2.s_div is null)
and table1.item_num = table2.i_num

As you said, if table2.s_num is not null, you will not join table2, and if table2.s_num is null matches, then will do table2.s_div = table1.store_div or table1.store_div is null, if this matches, it means table1.item_num = table2.i_num will be computed.

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103467

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

Upvotes: 1

j1rjacob
j1rjacob

Reputation: 430

SELECT A.*  
FROM table1 AS A JOIN table2 AS B ON (B.i_num = A.item_num)
    WHERE A.item_num IN (SELECT i_num FROM table1 WHERE s_num=null AND s_div=null);

Upvotes: 0

jelliaes
jelliaes

Reputation: 505

You can try this. Change OR to AND in WHERE clause if both s_num and s_div should be null

SELECT *
FROM TABLE1 T1
INNER JOIN TABLE1 T2
ON T2.I_NUM = T1.ITEM_NUM
WHERE T2.S_NUM IS NULL OR T2.S_DIV IS NULL

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

use table2 LEFT JOIN to table1

SELECT store_num = coalesce(t2.s_num, t1.store_num),
       item_num  = t2.i_num,
       store_div = colaesce(t2.s_div, t1.store_div),
       t1.price
FROM   table2 t2
       LEFT JOIN table1 t1 ON t2.i_num = t1.item_num

Upvotes: 0

Raffaello.D.Huke
Raffaello.D.Huke

Reputation: 552

just join is ok

select t1.* 
from
table1 t1
join table2 t2
on t1.item_num = t2.i_num
where t2.s_num is null and t2.s_div is null

Upvotes: 0

Related Questions