Column mapping across multiple ms sql tables
I am new to ms sql and I have two tables The first table has
id param1 indicator1 indicator2
1 a 1 2
2 z 3 4
2 z 7 2
3 a 8 9
4 z 7 9
5 y 9 6
The second table has
Param Indicator
a 1
b 2
null 3
null 4
I need a result set that will accomplish this:
For each row in table 1
if( param1 is in Param column)
print row from table 1
else
if(indicator1 in indicator column_
print row from table 1
if(indicator2 in indicator column)
print row from table 1
so the resulting dataset will be
id param1 indicator1 indicator2
1 a 1 2
2 z 3 4
2 z 7 2
3 a 8 9
I want a more efficient solution then
select distinct id, param1, indicator1, indicator2
from table1 t1, table2 t2
where param1= param or indicator1 = indicator or indicator2 = indicator
+3
Novice
source
to share
4 answers
Instead, Join
check the usability EXISTS
which works better.
SELECT *
FROM table1 t
WHERE EXISTS (SELECT 1
FROM table2 s
WHERE t.param1 = s.Param
OR t.indicator1 = s.Indicator
OR t.indicator2 = s.Indicator)
+1
P ரதீப்
source
to share
Use JOIN instead of comma separated tables:
SELECT DISTINCT Id, param1, indicator1, indicator2
FROM table1 t1
JOIN table2 t2 ON t1.param1 = t2.Param OR
t1.indicator1 = t2.indicator OR
t1.indicator2 = t2.indicator
OUTPUT:
id param1 indicator1 indicator2
1 a 1 2
2 z 3 4
2 z 7 2
3 a 8 9
SQL FIDDLE
0
Stanislovas Kalašnikovas
source
to share
SELECT * FROM TABLE1 tbl1 INNER JOIN TABLE2 tbl2 on tbl1.param1 = tbl2.param
-1
Dane
source
to share
SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.indicator
-1
jhole89
source
to share