how to write query for join two table and fetch the data in sql?

Question

I have two table in my database suppose

old_table

id  name        type
322 , shubham,   0

new_table

id member_id
322 , 5
322 , 7

so i have to get something like this

select c.id, cm.member_id,
       case when existes cm.member_id = 5 as new_table
from old_table c left join
     new_table cm 
     on c.id = cm.id 
where c.type = 0 
order by c.id desc
limit 200

Possible duplicate of [How can an SQL query return data from multiple tables](https://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables) — Jacob Colvin, Aug 28 '17 at 18:22

score 1 · Answer 1 · answered Aug 28 '17 at 18:31

The join seems correct instead for case
You should use a case when ... then ... end eg:

     select 
      c.id,
      cm.member_id,
      case when cm.member_id = 5 then cm.member_id  end  as new_table
    from old_table c 
    left join new_table cm  on c.id = cm.id 
    where c.type = 0 
    order by c.id desc limit 200

or

    select 
      c.id,
      cm.member_id,
      case when cm.member_id = 5 then cm.member_id else 0 end  as new_table
    from old_table c 
    left join new_table cm  on c.id = cm.id 
    where c.type = 0 
    order by c.id desc limit 200

how to write query for join two table and fetch the data in sql?

1 Answers1