Clever Geek Handbook

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


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


source


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


source


SELECT * FROM TABLE1 tbl1 INNER JOIN TABLE2 tbl2 on tbl1.param1 = tbl2.param

-1


source


SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.indicator

-1


source






More articles:


All Articles