Autor Zpráva
branor
Profil *
Ahojte, velmi prosim o pomoc, v databazach nie som az taky odbornik, ale obcas sa mi nieco podari.

Mam 2 tabulky.
Jedna sa vola "people" a obsahuje zoznam ludi v stlpcoch "name" a "people_id"
Druha sa vola "polozky" a obsahuje zaznamy v stlpcoch "people_id" a "kusy"
teda obe tabulky maju zhodny jeden stlpec "people_id"
v druhej tabulke su zaznamy, ktore obsahuju viacnasobne v stlpci "people_id" rovnake polozky

Ulohou je vypisat zoznam ludi a pri kazdom vypisat sucet hodnot zo stlpca "kusy" len z riadkov ktore obsahuju jeho konkretne "people_id"

Tabulka "people"

name people_id
Matus 1122
Tibor 6688


Tabulka "polozky"

people_id kusy
1122______4
6688______2
1122______3
1122______2
1122______5
6688______2

Vysledok KTORY POTREBUJEM VRATIT
Matus 14
Tibor 4


Dakujem pekne
Kajman
Profil
Budete potřebovat spojení tabulek (join) se správnou podmínkou, seskupení záznamů (group by) a agregační funkci (sum)
SELECT pe.name,
       Coalesce(Sum(po.kusy), 0) kusy
FROM   people pe
       LEFT JOIN polozky po
              ON pe.people_id = po.people_id
GROUP  BY pe.people_id,
          pe.name 
branor
Profil *
Dakujem, upravil som si dany select na moje veci, ale priznam sa ze mam problem vysledok zaradit do vypisu k inym vypisom z tabuliek -selekt ako taky funguje vyborne to som skusal:
$query_vypocet = MySQL_Query("SELECT 
       Coalesce(Sum(po.pocet), 0) pocet
FROM   peoples pe
       LEFT JOIN cards_entries po
              ON pe.people_id = po.people_id
GROUP  BY pe.people_id");
      $vypocet = mysql_fetch_array($query_vypocet);

komplet vypis PHP v ktorom to potrebujem - jedna sa o polozku vypocet

<table width="100%" border="0" cellspacing="1">
        
        <tr>
            <td class="thead">Priezvisko</td>
            <td class="thead">Meno</td>
      <td class="thead">Počet</td>
      <td class="thead">Poznámka</td>            
      <td class="thead">Storno</td>
        </tr>
            <?php
            $sql_where = "surname LIKE '%".$_REQUEST['surname']."%' ";
            $sql_where .= " AND name LIKE '%".$_REQUEST['name']."%' ";
            $sql_where .= " AND city LIKE '%".$_REQUEST['city']."%' ";
            $sql_where .= " AND annotation LIKE '%".$_REQUEST['annotation']."%' ";
            $sql_where .= " AND storno=".$_REQUEST['storno']." ";
            if(empty($_REQUEST['actual_from'])) $_REQUEST['actual_from']="0";
            if(empty($_REQUEST['rows_per_page'])) $_REQUEST['rows_per_page']="50";
            if(empty($_REQUEST['pocet_listkov'])) $_REQUEST['pocet_listkov']="0";
      
                        
            $query = MySQL_Query("SELECT  * FROM peoples WHERE $sql_where ORDER BY surname LIMIT ".$_REQUEST['actual_from'].",".$_REQUEST['rows_per_page']." ");
            
       $query_vypocet = MySQL_Query("SELECT 
       Coalesce(Sum(po.pocet), 0) pocet
FROM   peoples pe
       LEFT JOIN cards_entries po
              ON pe.people_id = po.people_id
GROUP  BY pe.people_id");
      $vypocet = mysql_fetch_array($query_vypocet); 
            
            $query_rows_count = MySQL_Query("SELECT  * FROM peoples WHERE $sql_where ");
            $rows_count = MySQL_Num_Rows($query_rows_count);
      echo MySQL_Error();
            
            $i=0;
            while($result = mysql_fetch_array($query)){
            if(($i%2)==1) {$bgclass="bg";}else{$bgclass="tvalue";};
            ?>
        <tr>
            <td class="<?php echo $bgclass?>"><?php echo $result['surname']?></td>
            <td class="<?php echo $bgclass?>"><?php echo $result['name']?></td>
            <td class="<?php echo $bgclass?>"><?php echo $result['vypocet']?></td>      
      <td class="<?php echo $bgclass?>"><?php echo $result['annotation']?></td>            
      <td class="<?php echo $bgclass?>"><?php echo select_yes("storno","view",$_REQUEST['storno'])?></td>
        </tr>
            <?php
            $i++;
            }
            ?>
    </table>
branor
Profil *
chyba je predpokladam v tom, ze sa mi biju selekty query a query_vypocet a neni spravne vytiahnuta vysledna hodnota, mohol by sa prosim na to niekto kuknut, ako tie 2 selekty zlucit? vobec netusim ako to spravne napisat...

DAKUJEM

<?php
            $sql_where = "surname LIKE '%".$_REQUEST['surname']."%' ";
            $sql_where .= " AND name LIKE '%".$_REQUEST['name']."%' ";
            $sql_where .= " AND city LIKE '%".$_REQUEST['city']."%' ";
            $sql_where .= " AND annotation LIKE '%".$_REQUEST['annotation']."%' ";
            $sql_where .= " AND storno=".$_REQUEST['storno']." ";
            if(empty($_REQUEST['actual_from'])) $_REQUEST['actual_from']="0";
            if(empty($_REQUEST['rows_per_page'])) $_REQUEST['rows_per_page']="50";
            
            
            $query = MySQL_Query("SELECT  * FROM peoples WHERE $sql_where ORDER BY surname LIMIT ".$_REQUEST['actual_from'].",".$_REQUEST['rows_per_page']." ");
            
      
           $query_vypocet = MySQL_Query("SELECT Coalesce(Sum(po.pocet), 0) pocet  FROM   peoples pe LEFT JOIN cards_entries po ON pe.people_id = po.people_id GROUP  BY pe.people_id");
      
      
      
      $query_rows_count = MySQL_Query("SELECT  * FROM peoples WHERE $sql_where ");
            $rows_count = MySQL_Num_Rows($query_rows_count);
      echo MySQL_Error();
            
      
      
            $i=0;
            while($result = mysql_fetch_array($query)){
            if(($i%2)==1) {$bgclass="bg";}else{$bgclass="tvalue";};
            ?>

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: