How to compare two DB tables of different number of rows based on one attribute in MySql?


Problem :

What I have:

1.I have two DB tables display and bid as listed below:

mysql> select * from display where gp_no = 1;

+-------+--------------+-----------+--------------------+---------------+-----------+----------------+
| gp_no | no_of_member | amount    | current_instalment | starting_date | member_no | member_name    |
+-------+--------------+-----------+--------------------+---------------+-----------+----------------+
|     1 |           15 | 375000.00 |                  2 | 2015-05-01    |         1 | Akansha Gupta  |
|     1 |           15 | 375000.00 |                  2 | 2015-05-01    |         2 | Akash Bansal   |
|     1 |           15 | 375000.00 |                  2 | 2015-05-01    |         3 | Ashish Gupta   |
|     1 |           15 | 375000.00 |                  2 | 2015-05-01    |         4 | Chavi Jain     |
|     1 |           15 | 375000.00 |                  2 | 2015-05-01    |         5 | Dhruv Goel     |
|     1 |           15 | 375000.00 |                  2 | 2015-05-01    |         6 | Mukul Gupta    |
|     1 |           15 | 375000.00 |                  2 | 2015-05-01    |         7 | Nancy Aggarwal |
|     1 |           15 | 375000.00 |                  2 | 2015-05-01    |         9 | Prateek Jain   |
|     1 |           15 | 375000.00 |                  2 | 2015-05-01    |        11 | Rajender Gupta |
|     1 |           15 | 375000.00 |                  2 | 2015-05-01    |        12 | S K Goel       |
|     1 |           15 | 375000.00 |                  2 | 2015-05-01    |        13 | Sadhna Goel    |
|     1 |           15 | 375000.00 |                  2 | 2015-05-01    |        15 | Sandeep Jain   |
|     1 |           15 | 375000.00 |                  2 | 2015-05-01    |        16 | Sunil Jain     |
|     1 |           15 | 375000.00 |                  2 | 2015-05-01    |        17 | Sunil Sharma   |
|     1 |           15 | 375000.00 |                  2 | 2015-05-01    |        19 | Sunita Gupta   |
+-------+--------------+-----------+--------------------+---------------+-----------+----------------+
15 rows in set (0.00 sec)

mysql> select date , member_no from bid where gp_no = 1 order by member_no asc;
+------------+-----------+
| date       | member_no |
+------------+-----------+
| 2015-06-01 |         7 |
| 2015-05-01 |        16 |
+------------+-----------+
2 rows in set (0.00 sec)

What I want :

  1. I want to compare the member_no from both the tables and where I get a match It should display date from table bid else it should display Not Withdraw. With the code I'm using , I'm able to display only first date i.e., only first match . I probably knows that its because of the mismatch in the no. of rows of both the tables.

This is what I'm getting

But it should also display the date in front of the member_no = 16.

PHP Code

$sql = "select * from display where gp_no = '$gp_no' ";
$result = mysqli_query($conn, $sql);

$s_no = 1 ; 
if (mysqli_num_rows($result) > 0)
{


 $s = " select date,member_no  from bid where gp_no = '$gp_no ' order by member_no asc"; 
 $r = mysqli_query($conn, $s);
 if (mysqli_num_rows($r) > 0)
  {
    ?> <h1>Welcome !!! Details of GROUP NO : <?php echo $gp_no ;  ?></h1> 
     <table  align = center ; style="width:50%">
     <tr><th><?php echo " Group Number ";?> </th><td><?php echo $gp_no ; ?></td></tr>
     <tr><th><?php echo " Number of Members ";?> </th><td><?php echo $no_of_member ; ?></td></tr>
     <tr><th><?php echo " Amount ";?> </th><td><?php echo $amount ; ?></td></tr>
     <tr><th><?php echo " Starting Date ";?> </th><td><?php echo $starting_date ; ?></td></tr>
     <tr><th><?php echo " Current Instalment ";?> </th><td><?php echo $current_instalment ; ?></td></tr>
     </table>
     <table  align = center ; style="width:50%" id = " display">
    <tr><th><?php echo "  Sr. Number ";  ?> </th><th><?php echo "  Member Number ";  ?> </th><th><?php echo "  Member Name ";  ?> </th><th><?php echo "  Bid Withdraw ";  ?> </th></tr>
   <?php
   /*$row_count = mysqli_num_rows($result);
   $row1_count = mysqli_num_rows($r);
   $remaining_rows = max($row_count, $row1_count);
   while($remaining_rows-- > 0)
{
    $row = mysqli_fetch_assoc($result);
    $row1 = mysqli_fetch_assoc($r); */
     while($row1 = mysqli_fetch_assoc($r))
     {
         while($row = mysqli_fetch_assoc($result))
    { 


      ?>
     <tr>
        <td><?php echo $s_no ; ?></td>

        <td><?php echo $row["member_no"] ; ?></td>

        <td><?php echo $row["member_name"] ; ?></td>
    <?php
     if($row["member_no"] === $row1["member_no"])
    {
    ?>
       <td><?php echo $row1["date"] ; ?></td>
    <?php 
     }
    else
     { ?> 
       <td><?php echo " Not withdrawn "; ?></td> 
       <?php } ?> 
   </tr> 
  <?php
       $s_no = $s_no + 1;
        }
        } 
        ?>
  </table>  
      <?php 
        }
            } 
    else
    {
    ?> <h1>  Members not associated yet. </h1> <?php
    }
   }
  mysqli_close($conn);
 ?>


Solution :

Try this query it should work as per your requirement

SELECT d.member_no, d.member_name, IFNULL(b.date, 'NOT WITHDRAWN')
FROM display d LEFT JOIN bid b ON d.member_no = b.member_no
and b.gp_no='1' where d.gp_no='1'

Mysql Tutorials

Mysql Howto..

How to collaborate on mysql schema?

PHP - How to specify MySQL row name instead of number in mysql_result

How to change a MySQL database to UTC?

how to create calculated field in mysql?

How to repair a MySQL table without using phpMyAdmin?

How do I $_POST in loop

how to update the value of one table when the value is inserted in another table using mysql?

Mysql how to select max id group by another id where boolean is false

How to ensure SQL operations are committed or aborted as a unit in PHP? [closed]

How to sync mysql table fields with prefix

Hyperlink forces file download - how to save file on server?

Smarty - How to add values from my form to mysql query?

How to profile MySQL

How to tell MySQL to use more indexes

how to load a single-column csv file into a specific column in mysql

How to design a movie database?

How to check verify that SQL query was ran in transaction?

How to know a certain row exists MySQL

How to match comma delimited value with other column in mySql

How to download, unzip and import to mysql dump all via piping?

How to make a nested query?

How to know if the insert function to mysql is done successfully?

MYSQL: how to retrieve all table rows + only the last row where column equals

How do you execute a sql file using mysql-ctl

HOW select min from cast varchar to int in mysql

How to join two MySQL tables grouped depending on a max() value

How to populate html combo box with mysql data

How to fetch the data and display it in a browser simultaneously using PHP and Smarty?

How to add a string containg a double quote to a sql database?

How does firebird db handles user authentication?