Mysql two tables, one unique field but overlapping data on query.. please help!
Posted on 2006-03-29
I have two normalised tables, The parent table contains Product Details with Product Code as the Primary field. The child table contains data for the packaging information of the products in table 1 and the records are made unique once again by the Product Code.
I am Using Coldfusion and this MYsql setup to query data for updating/editing etc.. The problem i am having is that due to the multiple records of each product in table 1 the query repeats this data on the screen. I was wondering if there is a method or query which i can use to join the tables in a way that only one record from table 1 is shown with its respective multiple records from table 2.
Query i am using at the moment:
Select * FROM tblProducts pdt
join tblProduct_Pack pck
ON pdt.ProductCode = pck.productCode
Previously using MS access this is very easy by joining the tables on a one-to-many relationship but using mysql i am having a difficult time organising the data.
All help will be much appreciated.