Help with INNER JOINS

Question

I’m having a problem with INNER JOIN.

When I do a search for a certain sale it brings me all sales related to the customer code, because it is the field "codcliente" that associates the 03 tables (Customer, Sale and Itemvenda), besides taking only a total value of sales.

I am publishing the access address of the page, so that friends can analyze and who can tell me what to do to get the result only of the researched sale.

The sales that are registered with the same customer are the 141, 146 and 147 all registered with the same code.

http://www.lccinformatica.com.br/prod_consulta_venda.php

Below I publish the code of the search page.

    <div align="left" style="margin:0 0 0 10px;"><h3>Consultar Venda</h3></div>

<div align="center" style=" padding:2px; width:655px; height:auto; float:left;">
    <div align="left" style=" padding:2px; width:315px; height:auto; float:left;">
        <label>Entre com o código da Venda</label>
            <form action="prod_consulta_venda.php" name="busca" method="post">
            <input size="6" type="text" value="" name="buscar"/>
            <input type="submit" name="busca" value="Buscar Venda"/>
        </form>
    </div>
</div>

<div align="left" style=" padding:2px; width:1000px; height:auto; border-top:solid 2px; float:left;">

<br />

<?php
    include 'conexao.php';

    if(isset($_POST['busca'])){
$buscar = $_POST['buscar']; 

$sql_listar = mysql_query("SELECT * FROM cliente
INNER JOIN venda
ON cliente.codcliente = venda.codcliente
INNER JOIN itemvenda
ON cliente.codcliente = itemvenda.codcliente
WHERE venda.codvenda LIKE '%$buscar%'")or die(mysql_error());

$total_registros = mysql_num_rows($sql_listar);

    if(mysql_num_rows($sql_listar) <= 0){

        echo "<meta http-equiv='refresh' content='0; URL= prod_consulta_venda.php'>
<script language='javascript'>
window.alert('Desculpe! Nenhuma Venda foi encontrada com esse código!');
</script>";

    }else{

    while($res = mysql_fetch_array($sql_listar)){

        $conteudotabela1 .= '<tr style="color:#090;">

                <td align="center">'.$res['codvenda'].'</td>

                <td align="center">'.$res['codcliente'].'</td>

                <td align="center">'.$res['datavenda'].'</td>

                <td align="center">'.$res['total'].'</td>

                <td align="center">'.$res['codproduto'].'</td>

                <td align="center">'.$res['quant'].'</td>

                <td align="center">'.$res['preco'].'</td>

                <td align="center">'.$res['nome'].'</td>

                <td align="center">'.$res['endereco'].'</td>

                <td align="center">'.$res['bairro'].'</td>

                <td align="center">'.$res['cidade'].'</td>

                <td align="center">'.$res['estado'].'</td>

                <td align="center">'.$res['cep'].'</td>

            </tr>';
    }}}


?>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252" />

<table class="tbllista" style="width: 15%">
    <thead>
        <tr align="center">
            <th style="width: 5%"></th>
            <th style="width: 5%">
        </tr>
    </thead>
</table>

<table class="tbllista" style="width: 100%">
    <thead>
        <tr align="center">
            <th align="center">Cód. Venda</th>
            <th align="center">Cód. Cliente</th>
            <th align="center">Data da Venda</th>
            <th align="center">Total da Venda</th>
            <th align="center">Cód. Produto</th>
            <th align="center">Quantidade</th>
            <th align="center">Preço Unitário</th>
            <th align="center">Nome</th>
            <th align="center">Endereço</th>
            <th align="center">Bairro</th>
            <th align="center">Cidade</th>
            <th align="center">Estado</th>
            <th align="center">Cep</th>
         </tr>
    </thead>
    <tbody>
            <?php echo $conteudotabela1; ?>

    </tbody>

My question is how to make so that I receive only the data of the consulted sale related to the code of the researched sale, regardless of whether it is the same customer or not.

I thank you all for your attention, wishing you Happy Holidays.

Answer 1

The correct modeling of your tables would be the customer code being associated with the sale (which is the header) and each sales item (details) being associated with the sale, something like this:

TABELA cliente (codcliente, nome, etc)
TABELA venda (codcliente, numvenda, data, etc)
TABELA itemvenda (numvenda, codproduto, quantidade, etc)

This way the sql to search the data would be adapted as follows (taken from your code)

SELECT * FROM cliente
INNER JOIN venda
ON cliente.codcliente = venda.codcliente
INNER JOIN itemvenda
ON venda.numvenda = itemvenda.numvenda
WHERE venda.codvenda LIKE '%$buscar%'