[Maria-discuss] KB Question: What Join?
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? - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Thanks. -- Daniel Bartholomew Google+ - http://gplus.to/dbart Twitter - http://twitter.com/daniel_bart MariaDB: An Enhanced Drop-in Replacement for MySQL Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledgebase - http://kb.askmonty.org Monty Program - http://montyprogram.com
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
participants (2)
-
Daniel Bartholomew -
Sergei Petrunia