OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Problem with join and alias

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
sentnel
Newbie
Newbie


Joined: 03 Oct 2010
Posts: 2

PostPosted: Sun Oct 03, 2010 8:28 pm    Post subject: Problem with join and alias Reply with quote

Hello everyone!

Im new to php mysql programing and Im stuck with something that may be simple for you guys but I cant find the way to obtain a "name value" of a column that stores a "int" that points to another table.

This query does what I need:


Code:
SELECT
    `jos_polnac_armastipo`.`arma`
FROM
    `polnac_db`.`jos_polnac_partediario`
    INNER JOIN `polnac_db`.`jos_polnac_armastipo`
        ON (`jos_polnac_partediario`.`arma_utilizada` = `jos_polnac_armastipo`.`id`);



returns this:

ARMA DE FUEGO
ARMA DE FUEGO
ARMA BLANCA
OBJETO CONTUNDENTE
ARMA BLANCA
CUCHILLO


Everything fine up to here. The problem comes with the following select, returns this:

2
2
1
6
1
4

Where each number (the #2 is the reference to ARMA DE FUEGO value in the "arma_utilizada" table). I can't get the below select to display the "ARMA DE FUEGO" value instead of the numbers.

Here is the "big" select, where I can only get the "numbers":


Code:
<?php
$SQL = "SELECT
    `jos_polnac_partediario`.`id` as id
    , `jos_polnac_partediario`.`fecha_partediario` as fechaparte
    , `jos_polnac_partediario`.`hora_partediario` as horaparte
    , `jos_polnac_partediario`.`titulo_partediario` as tituloparte
    , `jos_polnac_partediario`.`region_pn` as regionpn
    , `jos_polnac_partediario`.`departamento_pn` as deptopn
    , `jos_polnac_partediario`.`fecha_hecho` as fechahecho
    , `jos_polnac_partediario`.`hora_hecho` as horahecho
    , `jos_polnac_partediario`.`semana_hecho` as semanahecho
    , `jos_polnac_partediario`.`delito` as delitohecho
    , `jos_polnac_partediario`.`direccion_delito` as direcciondelitohecho
    , `jos_polnac_partediario`.`sector_delito` as sectordelitohecho
    , `jos_polnac_partediario`.`arma_utilizada` as armautilizadahecho
    , `jos_polnac_partediario`.`desplazamiento` as desplazamientohecho
    , `jos_polnac_partediario`.`placa_desplazam` as placadesplazahecho
    , `jos_polnac_partediario`.`numero_autores` as numeroautores
    , `jos_polnac_partediario`.`her_arm_fuego` heridasarmfuego
    , `jos_polnac_partediario`.`fallec_arm_fuego` as fallecarmfuego
    , `jos_polnac_partediario`.`her_arm_blanca` as heridasarmblanca
    , `jos_polnac_partediario`.`fallec_arm_blanca` as fallecarmblanca
    , `jos_polnac_partediario`.`her_obj_contun` as heridasobjcontu
    , `jos_polnac_partediario`.`fallec_obj_contun` as fallecobjcontu
    , `jos_polnac_partediario`.`her_acc_trans` as heridasacctrans
    , `jos_polnac_partediario`.`fallec_acc_trans` as fallecacctrans
    , `jos_polnac_partediario`.`vehic_robado_tipo` as vehicrobadotipo
    , `jos_polnac_partediario`.`placa_veh_robado` as placavehicrobado
    , `jos_polnac_partediario`.`chasis_veh_robado` as chasisvehicrobado
    , `jos_polnac_partediario`.`marca_veh_robado` as marcavehicrobado
    , `jos_polnac_partediario`.`color_veh_robado` as colorvehicrobado
    , `jos_polnac_partediario`.`afectado_nombre` as afectadonombre
    , `jos_polnac_partediario`.`afectado_apodo` as afectadoapodo
    , `jos_polnac_partediario`.`afectado_direccion` as afectadodireccion
    , `jos_polnac_partediario`.`afectado_genero` as afectadogenero
    , `jos_polnac_partediario`.`afectado_fecha_nac` as afectadofechanac
    , `jos_polnac_partediario`.`afectado_edad` as afectadoedad
    , `jos_polnac_partediario`.`afectado_cedula` as afectadocedula
    , `jos_polnac_partediario`.`afectado_ocupacion` as afectadoocupacion
    , `jos_polnac_partediario`.`tel_cel_personal` as afectadotelcelpers
    , `jos_polnac_partediario`.`tel_cel_flota` as afectadotelcelflota
    , `jos_polnac_partediario`.`tel_cel_casa` as afectadotelcelcasa
    , `jos_polnac_partediario`.`tel_cel_ofi` as afectadoteltrabajo
    , `jos_polnac_partediario`.`autor_nombre` as autornombre
    , `jos_polnac_partediario`.`autor_apodo` as autorapodo
    , `jos_polnac_partediario`.`autor_direccion` as autordireccion
    , `jos_polnac_partediario`.`autor_genero` as autorgenero
    , `jos_polnac_partediario`.`autor_fecha_nac` as autorfechanac
    , `jos_polnac_partediario`.`autor_edad` as autoredad
    , `jos_polnac_partediario`.`autor_cedula` as autorcedula
    , `jos_polnac_partediario`.`autor_ocupacion` as autorocupacion
    , `jos_polnac_partediario`.`autor_tel_cel_pers` as autortelcelpers
    , `jos_polnac_partediario`.`autor_tel_flota` as autortelflota
    , `jos_polnac_partediario`.`autor_tel_casa` autortelcasa
    , `jos_polnac_partediario`.`autor_tel_cel_ofi` autorteltrabajo
    , `jos_polnac_partediario`.`partediario` as partediario
    , `jos_polnac_partediario`.`observaciones` as partediarioobserva
    , `jos_polnac_partediario`.`preparadopor` as preparadopor
    , `jos_polnac_partediario`.`estatus_caso` as estatuscaso
    , `jos_polnac_partediario`.`cerrado_por` as cerradopor
    , `jos_polnac_partediario`.`fechahora_cerrado` as fechahoracerrado
    , `jos_polnac_partediario`.`supervisoria_zonas` as partesupervizona
    , `jos_polnac_partediario`.`fuente` as fuente
    , `jos_polnac_regionales`.`regional` as regionalesregionalpn
    , `jos_polnac_departamentos`.`departamento` as departamentosdptospn
    , `jos_polnac_supervisorias`.`supervisoria` as supervisupervizona
    , `jos_polnac_delitos`.`delito` as delitosdelito
    , `jos_polnac_sectores`.`sector` as sectoressector
    , `jos_polnac_armastipo`.`arma` as armatipoarma
    , `jos_polnac_desplazamiento`.`desplazamiento` as desplazadesplaza
    , `jos_polnac_genero`.`genero` as generogenero
    , `jos_polnac_fuente`.`fuente` as fuentefuente
FROM
    `polnac_db`.`jos_polnac_partediario`
    LEFT JOIN `polnac_db`.`jos_polnac_regionales`
        ON (`jos_polnac_partediario`.`region_pn` = `jos_polnac_regionales`.`id`)
    LEFT JOIN `polnac_db`.`jos_polnac_departamentos`
        ON (`jos_polnac_partediario`.`departamento_pn` = `jos_polnac_departamentos`.`id`)
    LEFT JOIN `polnac_db`.`jos_polnac_supervisorias`
        ON (`jos_polnac_partediario`.`supervisoria_zonas` = `jos_polnac_supervisorias`.`id`)
    LEFT JOIN `polnac_db`.`jos_polnac_delitos`
        ON (`jos_polnac_partediario`.`delito` = `jos_polnac_delitos`.`id`)
    LEFT JOIN `polnac_db`.`jos_polnac_sectores`
        ON (`jos_polnac_partediario`.`sector_delito` = `jos_polnac_sectores`.`id`)
    LEFT JOIN `polnac_db`.`jos_polnac_armastipo`
        ON (`jos_polnac_partediario`.`arma_utilizada` = `jos_polnac_armastipo`.`id`)
    LEFT JOIN `polnac_db`.`jos_polnac_desplazamiento`
        ON (`jos_polnac_partediario`.`desplazamiento` = `jos_polnac_desplazamiento`.`id`)
    LEFT JOIN `polnac_db`.`jos_polnac_genero`
        ON (`jos_polnac_partediario`.`afectado_genero` = `jos_polnac_genero`.`id`) AND (`jos_polnac_partediario`.`autor_genero` = `jos_polnac_genero`.`id`)
    INNER JOIN `polnac_db`.`jos_polnac_fuente`
        ON (`jos_polnac_partediario`.`fuente` = `jos_polnac_fuente`.`id`)";
      ?>
       
       
     <?php
   
   $result=mysql_query($SQL);
   while($fila = mysql_fetch_assoc($result)) {
   echo $fila['armautilizadahecho']; 
    echo "<br />";
   }
   
   ?>
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Mon Oct 04, 2010 2:51 am    Post subject: Reply with quote

Hello

I think you can find here information about SQL and joins

Romke
Back to top
View user's profile Send private message
sentnel
Newbie
Newbie


Joined: 03 Oct 2010
Posts: 2

PostPosted: Tue Oct 05, 2010 4:39 am    Post subject: Reply with quote

Thanks!
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group