The Ultimate Guide to High Availability with MariaDB

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 atleast 100 different joins that don't work. Can someone give me a clue?

Thanks

Richard

Answer Answered by Sergei Golubchik in this comment.

Answer from: https://lists.launchpad.net/maria-discuss/msg00746.html

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;

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.