repeated field query question

[shopwise]

my database contains a listing of products. some products are members of a productfamily as they are the same product except for a different size etc and some are not productfamily members.

the "products" table indicates which products are associated with which productfamily, if any, with the use of the productid (primary key), productfamily fields

with a many-to-many-relationship table named "products_images", I have specified which productid or productfamily every image_url should be associated with by using these fields:
images.image_url, products.productid, products.productfamily. so for some images i have matched up a productid and for some a productfamily, the latter, for when i would like a single image to repeat by each member of a productfamily.

what i would like to do is create a query which would repeat an image_url value (pulled from the products_images table) for every productid value (pulled from the products table) that is a member of the productfamily that is specified in the products_images table. i suppose this would be accomplished by joining the productfamily field of both tables.

how is such a query created

 

[lespaul]

It sounds like you have something like this:

[tt]
Products
ProductID
ProductFamilyCode

Product_Images
ImageID
URL
ProductFamilyCode[/tt]

you would then just JOIN and get the results you want:

SELECT * FROM 
Products P
INNER JOIN Product_Images I ON P.ProductFamilyCode = I.ProductFamilyCode

you may want to read Understanding SQL Joins

Leslie