MySQL Select over two tables
Posted: Sun Jun 22, 2003 6:39 pm
I'm having trouble with a JOIN thingy, and it's making my head hurt.
I have a lot of categories in a table. They each have a category id and a name, and they can appear within other categories in my listings.
In order to determine where a category is within the structure of the site, I have a table, catroute, which lists each of the categories 'above' the one being viewed.
Now, what I want to do is grab the id of a category, with just its name and the names of the categories above it. So say I'm looking the Bream category located at Sea > Fish > Bream. All I have is the names of those categories. I need to select the ID of the bream category.
The catroute table lists category ids. It lists in one column the id of the category we are storing information about, and in the other column the ids of it's ancestors. So in this case, the cat_id column contains the ID of the Bream category, and there are two rows in this table. The cat_id2 column contains the ids of the Sea and Fish categories. So the SQL I am trying is this:
Can anyone help? I've tried lots of variations on this, but think my brain has melted.
I have a lot of categories in a table. They each have a category id and a name, and they can appear within other categories in my listings.
In order to determine where a category is within the structure of the site, I have a table, catroute, which lists each of the categories 'above' the one being viewed.
Now, what I want to do is grab the id of a category, with just its name and the names of the categories above it. So say I'm looking the Bream category located at Sea > Fish > Bream. All I have is the names of those categories. I need to select the ID of the bream category.
The catroute table lists category ids. It lists in one column the id of the category we are storing information about, and in the other column the ids of it's ancestors. So in this case, the cat_id column contains the ID of the Bream category, and there are two rows in this table. The cat_id2 column contains the ids of the Sea and Fish categories. So the SQL I am trying is this:
Code: Select all
SELECT catroute.cat_id FROM catroute LEFT JOIN categories ON catroute.cat_id2 = categories.cat_id WHERE categories.cat_name="Bream" AND categories.cat_name="Sea" AND categories.cat_name="Fish" limit 1