Might there have been an easier way to do this? I don't know if it's ugly or beatiful. I really can't believe that it worked. But it did.

$sql="
SELECT sub_cats.sub_cat_id, sub_cat_name, super_cat, cat_name, p_title
FROM products INNER JOIN (((categories
INNER JOIN cat_index ON categories.cat_id = cat_index.cat_id)
INNER JOIN sub_cats ON cat_index.sub_cat_id = sub_cats.sub_cat_id)
INNER JOIN sub_cat_index ON sub_cat_index.sub_cat_id = sub_cats.sub_cat_id)
ON products.p_id = sub_cat_index.p_id
WHERE products.p_id = $_GET[p_id]";

Actually it might not be that massive, but my head is still hurting from it. I am new to SQL and don't know if there are any shortcuts to this sort of thing or not.

It can probably be smaller, but I'd like to see more details about the database structure. Do you really need to join everything? You may not need to join with indexes and you can probbaly use NATURAL JOIN insted of your JOINs. But my opinion may change after seeing table structures.

Here is the structure of this part of the database:

products
product_index; associates products with sub_cats
sub_cats
sub_cat_index; associates sub_cat with categories
categories

The reason I used the indexes, which are actually associations, was that I wanted to be able to add each product to more than one sub_cat and add each sub_cat to more than one category. The reason I used the aforementioned query was to display "category->sub_cat->product" at the top of a product page. I could have done three seperate, simpler queries to pull each name individually but I would assume it would be better programming to run a single query to get all the information than querying the server 3 times.

So it looks that your query is the only one possible to do this with this database structure (above the same in a different way).

I didn't learn NATURAL JOIN in my rudimetary self study of SQL. How is different from INNER JOIN?

When using NATURAL JOIN you don't specify ON. The tables are joined using column(s) from the tables having the same name. For example, there are categories.cat_id and cat_index.cat_id in your tables. Two columns have the same name: cat_id. When you NATURAL JOIN them, the tables will be joined using cat_id from both of them.
It's handy plus (more important) - it's usually faster.


Warning here: when using natural join always make sure you're joining using the columns you want to (is there's another pair of columns with the same name they will be also used).

Mara, what's the behaviour of a natural join? Is it a left join, right join or something else?

Ups...you asked the quiestion a month ago. I'll answer now for future reference mostly, I guess.

Natural join is something different than left or right join. In fact, you can write 'left natural join' and so on. It makes the join operation use columns with the same name from both tables (and makes the operation faster because of this).