parts that belong to a assembly

carlosgil5

Hi guys, total newbie here. I have two mysql fields 'assemblys' and 'parts'. What I want to do is show in a report the parts that belong to an assembly. each assembly has a unique code (name) and each part has a unique code (name). However, the same part can be part of a another different assembly. Hope I explained myself correct. How would I aproach this?

Assembly has 3 fields

assemblyid | description | weight |

parts has 5 fields

devinemke

what you have here is a standard many-to-many data relationship: multiple parts that can be linked to multiple assemblies. create a 3rd table that links parts and assemblies using the primary keys field from each table. this is a pretty standard way of handling many-to-many relationships.

carlosgil5

thank you very much, I will read about it.

djjjozsi

if you make a 3th table,

FK: foreign key
PK: primary key

name: connector, it has two fields:
part_id // FK you put into this the part_id's value
assembly_id //FK and here goes the assembly ID value.

in your parts field, lets take an ID field,
part_id //PK
part
description
grade
lenght
weight

and in Assembly i put another field, called assembly_code, if its not needed, delete it.
assemblyid //PK
description
weight
assembly_code

select the parts, and count how many assemblies connected (for example a part code is aaaa1):

SELECT parts.part, Count(connector.assembly_id) AS CountOfassembly_id
FROM parts INNER JOIN connector ON parts.part_id = connector.part_id
GROUP BY parts.part
HAVING (((parts.part)='aaaa1'));

select one part, and list the assemblies where the part_id is 1 : (part_id is not equal with your park code)

SELECT parts.part, parts.part_id, Assembly.description, Assembly.weight, Assembly.assembly_code
FROM (parts INNER JOIN connector ON parts.part_id = connector.part_id) INNER JOIN Assembly ON connector.assembly_id = Assembly.assemblyid
GROUP BY parts.part, parts.part_id, Assembly.description, Assembly.weight, Assembly.assembly_code
HAVING (((parts.part_id)=1));

carlosgil5

okay, after reading about many to many relationships in "build your own database driven website using php & mysql" I need to ask about defining the lookup table needed to join the tables in the relationship. I already created the table (assemblys_catalog) and created two fields ( 'idassembly' and idpart'), according to the instructions on the book, I need to build a query but am lost about the way I should indicate to show me the row from assembly and the rows from parts to indicate that a certain assembly is made from certain parts. thanks for helping a newbie, I just started reading and trying mysql.

carlosgil5

thanks for your help, I will analyze your code, looks intimidating right now but am sure it will become much clearer to me after some tought.

djjjozsi

and you might use database query designer programs.

if you have question after reading a mysql reference,

ask for more.