Re: [Maria-discuss] KB Question: What Join?

18 Feb 2013


      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