0

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 } ?>
dachi
  • 1,604
  • 11
  • 15
SidlakMonster
  • 15
  • 5

3 Answers3

3

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.

Kami
  • 19,134
  • 4
  • 51
  • 63
  • This will only get the data where b.data2 exists. I also want to retrieve data where b.data doesn't exist and it will return a value with false inside it. – SidlakMonster Mar 05 '14 at 11:07
  • @user3382461 can you please clarify that what is data2 containing true or false or 0 – Prafulla Mar 05 '14 at 11:12
  • @user3382461 Updated the answer, but having read the question again, where is the `value` column? I assumed you were referring to the value of `b.data2`, but please clarify. – Kami Mar 05 '14 at 11:14
  • There is no 'value' column. It's like this: If 'a.id = b.a_id' (note: 'b.a_id' is based on 'a.id') Then, the table should return all the values from 'a' PLUS 'b.data2' and 'value = true' If 'a.id != b.a_id' Then, the table should return all the values from 'a' PLUS 'b.data2 = 0' and 'value = false' given that 'b.data2' and 'value' is an added column when returned. – SidlakMonster Mar 05 '14 at 11:24
  • @user3382461 Thanks for clarifying. I have updated the sql code to reflect. – Kami Mar 05 '14 at 11:45
  • I got an error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' IFNULL(mt.amt, `0)` as mt.amt FROM (`transaction` as t) LEFT OUTER JOIN `mtrans' at line 1 – SidlakMonster Mar 05 '14 at 12:00
  • @user3382461 you are transposing the SQL above to another form, ensure you are doing so correctly. I suspect the error is because `as mt.amt` should be `as amt`. – Kami Mar 05 '14 at 12:08
  • I still got an error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' IFNULL(mt.amt, `0)` as mtamt FROM (`transaction` as t) LEFT OUTER JOIN `mtrans`' at line 1 – SidlakMonster Mar 05 '14 at 12:20
  • @user3382461 Update your question with the code you are using after adding suggestions from my answer. – Kami Mar 05 '14 at 12:24
0

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

Prafulla
  • 600
  • 7
  • 18
0
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:

Community
  • 1
  • 1
ZeroBased_IX
  • 2,667
  • 2
  • 25
  • 46