CODEWITHSUNDEEP
mysql
Juned Ansari
Juned Ansari

Reputation: 5283

fetch data from table on the basis of condition in mysql

consider my scenario like i have 3 tables table1, table2, table3 i want to fetch some colums from table2 or table3 on the basis of some condition

like 

select tb1.*, 
if(tb1.status='true' then tb2.name else tb3.name) 
from table1 tb1, table2 tb2, table3 tb3
where tb1.aid=tb2.aid and tb1.aid=tb2.aid

in short i want to display some column from either table2 or table3 on the basis of condition

Upvotes: 0

Views: 44

Answers (3)

Paul Maxwell
Paul Maxwell

Reputation: 35563

SELECT
      tb1.*
    , CASE
            WHEN tb1.status = 'true' THEN tb2.name
            ELSE tb3.name
      END AS var_name
FROM table1 tb1
      INNER JOIN table2 tb2 ON tb1.aid = tb2.aid
      INNER JOIN table3 tb3 ON tb2.aid = tb3.aid

Use a case expression

BUT, you also need to look hard at how you are joining the tables. There are 2 things to note:

  1. stop using comma separated lists of tables, there is a more precise syntax available for joins
  2. you currently (in the question) don't have a proper join to table3

Upvotes: 1

sagi
sagi

Reputation: 40471

You can use CASE EXPRESSION :

SELECT tb1.*,
       CASE WHEN tb1.status = 'true' THEN tb2.name ELSE tb3.name END as `name`
FROM table1 tb1
INNER JOIN table2 tb2 
 ON(t1.aid = tb2.aid)
INNER JOIN table3 tb3
 ON(tb1.aid = tb3.aid)

Or with IF() like you wanted :

SELECT tb1.*,
       IF(tb1.status='true' ,tb2.col1,tb3.col1) as col1,
       IF(tb1.status='true' ,tb2.col2,tb3.col2) as col2,
       IF(tb1.status='true' ,tb2.col3,tb3.col3) as col3
       .....

Also, try to avoid the use of implicit join syntax(comma separated) and use the proper syntax of a join, this will help you avoid mistakes like the one you did (compared both conditions to tb2 instead of one to tb2 and one to tb3

Upvotes: 1

user3678853
user3678853

Reputation: 69

if(tb1.status='true',tb2.name,tb3.name) as name

Upvotes: 1

Related Questions