SQL表连接

How do I input in SQL where I got this 2 tables, table a and table b.

table a: 

+----+-------+--------+--------+
| id |  name |  data1 |  data2 |
+----+-------+--------+--------+

table b: 

+----+------------+-------+--------+--------+
| id |  member_id |  a_id |  data1 |  data2 |
+----+------------+-------+--------+--------+

So I want to get all the values from table a. I also want to get data2 and value = true from table b where a.id = b.a_id. If a data from table a doesn't have a value from table b where a.id = b.a_id, data2 = 0, value = false.

If this could all happen using SQL that would be great but if not, at least tell how you would solve the problem using PHP or codeigniter or just post your concept/algorithm. Thank you in advance.

Edit2:

Okay I got it. Thanks everyone below who shared their ideas. They helped a lot:

model:

    function getData2(){
    $id = $this->uri->segment(3);
    $this->db->select("t.*, mt.mid as mid, mt.amt as mtamt");
    $this->db->from("transaction as t");
    $this->db->join("mtrans as mt", "mt.tid = t.id and mt.mid = '$id'", "left outer");
    $query = $this->db->get();
    $data['info2'] = $query->result_array();

    return $data;
}

view:

<?php foreach ($info2["info2"] as $record) { ?>
        <tr>
            <td><?php echo $record["id"]; ?></td>
            <td><?php echo $record["name"]; ?></td>
            <td><?php echo $record["type"]; ?></td>
            <td><?php echo $record["amt"]; ?></td>
            <?php foreach ($info1["info1"] as $stat) { 
                if($record["mid"] == $this->uri->segment(3)){?>
            <td><?php echo $stat["mtamt"]; ?></td>
            <td><?php echo $stat["user"]; ?></td>
            <td><?php
            if(!$stat["mtamt"]){
                echo "Unpaid";
            }
            if($stat["mtamt"] < $record["amt"]){
                echo "Partial";
            }
            if($stat["mtamt"] == $record["amt"]){
                echo "Paid";
            }
            ?></td>

            <?php } } ?>
        </tr>
        <?php } ?>

Try something like

select
  a.*,
  IFNULL(b.data2, 0) as b_data2,
  IF(b.data2 IS NULL, false, true) as value
from a left outer join b
on a.id = b.a_id

It joins both the tables based on the keys and retrieves the relevant records.

Updated to allow for null values. IFNULL checks that a value is null, if it isnt, then return value, otherwise returns the second parameter, in this case false.

The IF function works like an if code statement, returns the first parameter if equality is true, second if it isnt.

I am assuming the b.data2 can be null, without a detailed table structure it is all guesswork.

try this query

SELECT a1.id, a1.name, a1.data1, a1.data2, (b1.data2) data FROM a a1
INNER JOIN b b1 ON b1.id= a1.a_id
WHERE b1.data2 = true

I took alias name to 2nd table's attribute as data it will simple for you

select 
a.id, 
a.name, 
a.data1,
a.data2
from tablea a 
inner join (select * from table b where data2 = True) b on a.id = b.id

A little rough and perhaps doesn't contain all the data you require though that's the best I deduced from what you gave me and should give you a good start point with joins. I'm sorry I couldn't test it since I currently don't have an environment.

You use an inner join when you want to link 2 tables where both of the criteria match: a.id = b.id

If you don't need them to match you would use an outer join. More resources: