I would like to use a table as a foriegn key for two other tables. I would like to query the first table and use those results in querys for the other tables. This probably isn't sounding too clear. Let me see if I can give an example.

I have a table that has a list of product releases. This table has id#, release name, and whether the release is visible or not (visible for customers).

My second table would have names of categories of a product. This would include id#, name of category, release id# (fk)

My third table would have names of products. This would include id#, product name, category id (fk), release id (fk), image file name.

So I want to query the first table and find out which releases are visible, take those results (id #s) and query the other tables to show/list products or categories tied to that release. The problem I am having is when more than one release is visible. I don't know how to take those multiple results and put them in the next query. Any thoughts??

Thanks!

Brian

Would I be correct that a product is in one category, and a category is in one release? If so, be careful with release id in the products table.

One approach would be a join between two or all three tables. That would give you a single query, but it may take some parsing on the client end, depending on how it's displayed.

If that doesn't fit your needs, also look into Variable IN (item1, item2, ...)

Thanks for the post. I will give it a try.

The reasoning for the release id in the products table is that just because a product can be in a category in a release, but not in the release. Like if a product is added to a category in a future release. I might just want that one new product to show up to customers and not the other products that were in the last release under that category.

I will let you know if I get this worked out.

-b

It worked. Here is the query I came up with so far. This queries the product categories and gives me ones in a release or releases.

SELECT group_id, name, releases.release_id, releases.visible FROM releases, paper_group where releases.visible = 'y' and releases.release_id = paper_group.release_id

Thanks for the help!

-b

You may want to use EXPLAIN SELECT to make sure the query is working from an index. Joins can get very nasty very quickly if mysql needs to scan the tables to build them.