1

I have two tabs named respectly liste_pieces and client_pieces.

The first contain a list of many elements about 1000. the second contain some element of the first.

Actually I'm doing a query to fullfill the second tab with the first.

Also I would like to display all element from the first that are not in the second.

I've thought I could do a joint pas a joint will only display elements that are in both tab.

And actually that is not what I'm looking for.

I've done that query for the moment

      <table id="box-table-a" width="100%">
    <tr>
        <th scope="col" width="50%">LISTE DES PI&Egrave;CES</th>
        <th scope="col" width="50%">AJOUT ?</th>
    </tr>

<?php $sql="SELECT * FROM `liste_pieces`";
$result=mysql_query($sql) or die;$i=0;
while($data=mysql_fetch_assoc($result))

{?>
<tr>
    <td><?php echo $data['libelle'] ; ?></td>
    <td style="text-align:center" align="center">
  <p align="center">  <input type="hidden" name="piece[<?php echo $i ; ?>]" value="0" /><input type="checkbox" class="checkbox" value="1" name="piece[<?php echo ++$i ; ?>]" id="piece[<?php echo ++$i ; ?>]" /></p></td>

</tr>
<?php } ?>

But this display to me all elements from the first tab. I do not really know how to remove elements that exists in the second tab.

I have the column id on liste_pices that is the same in the second tab except that it is called id_piece.

I'm a bit lost.

I do not know how to process.

Receive All my utmost Respect.

Kind regards.

SP.

Stanislas Piotrowski
  • 2,595
  • 10
  • 40
  • 59

2 Answers2

1

This query should do the trick for you. Yes, it is a join query by the way that you are asking for:

select
    a.col1,
    a.col2,
    b.col1 // etc
from
    liste_pieces a
        join client_pieces b
            on a.id=b.id_peice
where b.id_piece is null

Here is an example from my database:

mysql> select id, brand from cars;
+----+-------+
| id | brand |
+----+-------+
|  1 |     2 |
|  2 |     1 |
|  3 |     3 |
|  4 |     4 |
|  5 |     2 |
|  6 |     5 |
|  7 |     1 |
|  8 |     2 |
|  9 |     2 |
| 10 |     5 |
| 11 |     2 |
+----+-------+
11 rows in set (0.00 sec)


mysql> select id, brand from brands;
+----+--------+
| id | brand  |
+----+--------+
|  1 | Ford   |
|  2 | Toyota |
|  3 | Nissan |
|  4 | Smart  |
|  5 | BMW    |
|  6 | Holden |
+----+--------+
6 rows in set (0.00 sec)

select
    a.brand
from
    brands a
        left join cars b
            on a.id=b.brand
where
    b.brand is null

+--------+
| brand  |
+--------+
| Holden |
+--------+
1 row in set (0.00 sec)

Also: I just wrote a really lengthy question and answer that covers this exact sort of question which I hope will help to clarify how joins work on tables and how to get information from multiple tables in your database!

Community
  • 1
  • 1
Fluffeh
  • 33,228
  • 16
  • 67
  • 80
0

Use queries like this

"select * from liste_pieces where column_name not IN (select column_name from client_pieces)"

and

"select * from client_pieces where column_name not IN (select column_name from liste_pieces )"

This will solve your problem, the column_name should present in both table with same datatype.

Yogesh Suthar
  • 30,424
  • 18
  • 72
  • 100