need help querying two separate but related tables!!!

[websurfer]

hello, all:
I need help retrieving records from a catalog-like format I am building. Should be soemthing simple i think, but just cant get it...
Here is the thing: I have a mysql "Products" table with each product's info in it, and another "images" table that has all the image/paths for all products in general (images share the same "products" id to tie them together). So that, one product could have several images associated with it, whose paths reside in the "images" table. My problem is: how do I display back the product record along with all the images that go with it, since they are in different tables. A JOIN, or a UNION, or GROUP BY doesnt do it...

Please help.

see here basic tables structure:

Products table name: item

item_id, description, title, quantity, size, price, shipping
12, painting, Horse and Barn, 1, 18 X 24, 200, 10
13, painting, Mother and Child, 1, 12 X 12, 150, 10
14, painting, Bunny Rabbit, 1, 12 X 16, 100, 10

Images table name: item_images
item_id, image_id, image_file
12, 100, horses1.jpg
12, 101, horses2.jpg
12, 102, horses3.jpg
13, 103, mother1.jpg
13, 104, mother2.jpg
14, 105, rabbit.jpg

as you can see images and products table are linked by the item_id field. So how do I display record #12 (Horse and Barn) with all it's respective images???

thanks!!

[phper]

I would suggest using an Inner Join.

SELECT * FROM item INNER JOIN item_images ON item.item_id = item_images.item_id WHERE item.item_id = '12'

[websurfer]

thanks Phper.. gonna try this out; but doesnt this display back 3 records (item #12), as oppose to just the one-product-info record with it's 3 associated images??

Anyways, gonna try it a bit later today, let you know how it goes...

Thanks!

[phper]

If you want to just retrieve 1 image then just use the LIMIT feature.

e.g Limit 0,1

Regards!