Page 1 of 1

MySQL Select over two tables

Posted: Sun Jun 22, 2003 6:39 pm
by ILoveJackDaniels
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:

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
Can anyone help? I've tried lots of variations on this, but think my brain has melted.

Posted: Mon Jun 23, 2003 4:44 am
by ILoveJackDaniels
I think I've found where I'm going wrong (at least in one place). I'm trying to select a value from a column where another column equals each of several values, which is an impossible where clause.

So how do I select a value from a column where a different column equals each of several values?

Posted: Mon Jun 23, 2003 3:58 pm
by releasedj
If you want a row where that column contains 1 of the three strings simple replace the AND with OR.

If you want to find rows where that column contains all three use a LIKE statement:

Code: Select all

... WHERE categories.cat_name LIKE "%Bream%" AND categories.cat_name like "%Sea%" AND categories.cat_name LIKE "%Fish%"

Posted: Mon Jun 23, 2003 4:19 pm
by ILoveJackDaniels
I'm finding this really hard to explain...

Would that above only match rows where the value was something like 'Sea Fish'?

Below:

4 Sea
4 Fish
2 Sea
3 Fish
1 Monkey

The above is in a database. I want to grab the value in the first column which matches both Sea and Fish. Which should be 4. Not 2, and not 3. I'm not sure I can explain it any better...

Posted: Tue Jun 24, 2003 4:50 am
by releasedj
This won't be possible unless you use a database that supports sub-selects. MySQL does not.

The only way would be to fetch all rows that contain one of the strings and then using PHP to identify the numbers with the 2 strings.

Posted: Tue Jun 24, 2003 5:01 am
by ILoveJackDaniels
Ok, thanks. I've gone sufficiently mad, and I actually did what you suggested yesterday after pretty much giving up on the idea altogether....