MySQL Select over two tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
ILoveJackDaniels
Forum Commoner
Posts: 43
Joined: Mon May 20, 2002 8:18 am
Location: Brighton, UK

MySQL Select over two tables

Post 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.
User avatar
ILoveJackDaniels
Forum Commoner
Posts: 43
Joined: Mon May 20, 2002 8:18 am
Location: Brighton, UK

Post 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?
User avatar
releasedj
Forum Contributor
Posts: 105
Joined: Tue Jun 17, 2003 6:35 am

Post 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%"
User avatar
ILoveJackDaniels
Forum Commoner
Posts: 43
Joined: Mon May 20, 2002 8:18 am
Location: Brighton, UK

Post 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...
User avatar
releasedj
Forum Contributor
Posts: 105
Joined: Tue Jun 17, 2003 6:35 am

Post 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.
User avatar
ILoveJackDaniels
Forum Commoner
Posts: 43
Joined: Mon May 20, 2002 8:18 am
Location: Brighton, UK

Post 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....
Post Reply