CODEWITHSUNDEEP
sqlsql-server-2008if-statementcase
user2525015
user2525015

Reputation: 281

Conditionally select from one table or another table

I have two tables

TableA Column1, Column2,Column3, Column4
TableB Column1, Column2,Column3, Column4

Query to modify:

Select Column2,Column3, Column4
From TableA

How would I modify the above query to select Column3 from TableB if the value of TableA.Column2 exists in TableB? It would also have to be the max value of TableB.Column1.

TableA
Column1 Column2 Column3 Column4
1        A      zebra     apple
2        C      lion      orange
3        R      giraffe   banana

TableB
Column1 Column2 Column3 Column4
1       Q       snail     rock
2       C       frog      stone
3       Z       giraffe   tree
4       C       walrus    limb

Result
Column2          Column3   Column4
A                zebra     apple
C                walrus    orange
R                giraffe   banana

It might be something like:

   Select TableA.Column2,Case When Exists(Select TableB.Column3 From Table3 
   Where        Table2.Column = TableB.Column2 ) Then Select Table2.Column3 Where   [Table2.Column1 is  max value], Column4
   From TableA

Upvotes: 2

Views: 8148

Answers (1)

CD Jorgensen
CD Jorgensen

Reputation: 1391

Something like this below:

select
    a.column2
    , case when b.column2 is not null then max(b.column3) else a.column3 end
    , a.column4
from
    tableA a
    left join tableB b
        on a.column2 = b.column2
group by
    a.column2
    , a.column3
    , a.column4
    , b.column2

If this is a homework assignment, as the question seems, please study the code to understand why and how and it works.

Upvotes: 3

Related Questions