maria-discuss team mailing list archive

[Sergei Petrunia <psergey@xxxxxxxxxxxx>]

On Mon, Feb 18, 2013 at 11:33:29AM -0500, Daniel Bartholomew wrote:
> The following question has been posted to the Knowledgebase:
> 
> https://kb.askmonty.org/en/what-join/
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> I have a table called called ContenidosDeModelos with fields
> 
> +------------+-------------+------+-----+
> | Field      | Type        | Null | Key |
> +------------+-------------+------+-----+
> | CdMID      | int(5)      | NO   | PRI |
> | ModeloID   | int(5)      | NO   | MUL |
> | ArticuloID | int(5)      | NO   | MUL |
> | Cantidad   | float(10,3) | NO   |     |
> +------------+-------------+------+-----+
> which in turn is related on the field ArticuloID to a table called
> CatArticulos with fields
> 
> +-----------------------+--------------------------------------
> | Field                 | Type                                
> +------------------------+-------------------------------------
> | ArticuloID            | int(5)                              
> | ArticuloCodigo        | char(7)                            
> | ArticuloNombre        | varchar(45)                       
> | ArticuloDesc          | varchar(75)                      
> | ArticuloDeshabilitado | char(1)                         
> | ArticuloUnidad        | enum('pz','mts','kg','ton','m3','lts')
> +-----------------------+--------------------------------------
> I want a listing of ALL of table CatArticulos (1300+ records) with the
> value of the field Cantidad from the table ContenidosDeModelos for a
> specified ModeloID (3 - 10 records) when there is a common ArticuloID
> otherwise Cantidad should be NULL when there is no corresponding
> ArticuloID in ContenidosDeModelos.
> 
> I have written at least 100 different joins that don't work. Can someone
> give me a clue?
> 

How many entries does the table ContenidosDeModelos have for given values of
ModeloID and ArticuloID? If there is always one element, you can use 
something like:


select 
  CatArticulos.*, 
  ContenidosDeModelos.Cantidad
from 
  CatArticulos left join 
  ContenidosDeModelos ON (
  CatArticulos.ArticuloID=ContenidosDeModelos.ArticuloID AND
  ContenidosDeModelos.ModeloID= $specified_value) ;


If there are multiple, I'd use

select 
  CatArticulos.*, 
  (select group_concat(ContenidosDeModelos.Cantidad separator ',')
   from ContenidosDeModelos
   where
     CatArticulos.ArticuloID=ContenidosDeModelos.ArticuloID AND
     ContenidosDeModelos.ModeloID= $specified_value
  ) as Cantidad
from 
  CatArticulos;



BR
 Sergei
-- 
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog