Please explain this query

[petrik]

Can someone please explain what is going on here?

mysql> SELECT cat.id, cat.name, cat.description
-> FROM categories cat, categories parent
-> WHERE cat.parent_id = parent.id
-> AND parent.name = ‘Fruits’;

The table’s colums are
±—±----------±---------±--------------------------------+
| id | parent_id | name | description |
±—±----------±---------±--------------------------------+

Where does the cat.id, and categories cat, categories parent and all that come into it?

This is an exert from the following:

***** START EXERT *****

Okay, now let’s see what we have again:

mysql> SELECT * FROM categories;
±—±----------±---------±--------------------------------+
| id | parent_id | name | description |
±—±----------±---------±--------------------------------+
| 0 | 0 | Top | This is the top level category. |
| 1 | 0 | Fruits | Fresh and tasty fruits. |
| 2 | 0 | Snacks | Tasty snacks. |
| 3 | 1 | Apples | Yummy crunchy apples. |
| 4 | 1 | Berries | Yummy berries. |
| 5 | 2 | Chips | Crunchy Greasy Treats. |
| 6 | 2 | Icecream | Great on a hot summer day. |
±—±----------±---------±--------------------------------+

Let’s practice writing some more interesting SELECT queries. For example, let’s find all the sub-categories under the [Fruits] category:

mysql> SELECT cat.id, cat.name, cat.description
-> FROM categories cat, categories parent
-> WHERE cat.parent_id = parent.id
-> AND parent.name = ‘Fruits’;
±—±--------±----------------------+
| id | name | description |
±—±--------±----------------------+
| 3 | Apples | Yummy crunchy apples. |
| 4 | Berries | Yummy berries. |
±—±--------±----------------------+

Note that was a little unnecessary because we already knew that the ID of the [Fruits] category was 1, we could have just run the query:

mysql> SELECT * FROM categories WHERE parent_id = 1;

to do the same thing, but that was for fun so that we could practice our joins

**** END EXERT ****

Which comes from the website
http://www.devshed.com/c/a/PHP/Building … Catalog/5/

[Zyppora]

SELECT cat.id, cat.name, cat.description
FROM categories cat, categories parent
WHERE cat.parent_id = parent.id
AND parent.name = ‘Fruits’

In the query, you have two ‘tables’, named cat and parent (and they happen to refer to the same table). So what this query is doing, is:

“Give me the ID, name and description of all entries where the parent’s category is ‘Fruits’.”

SELECT cat.id, cat.name, cat.description -> Give me the ‘ID’, ‘name’ and ‘parent’
FROM categories cat, categories parent -> from the categories table as referred to by ‘cat’
WHERE cat.parent_id = parent.id -> where the ‘parent ID’ of ‘cat’ equals the ‘ID’ of ‘parent’
AND parent.name = ‘Fruits’ -> and where the ‘name’ of ‘parent’ is ‘Fruits’

[petrik]

Ok well my table is like this:

| category_ID | category | parent_ID |

and I rewrote the query like this

SELECT cat.category_ID, cat.category
FROM category cat, category parent
WHERE cat.parent_ID = parent.category_ID
AND parent.category = ‘Fruits’

Which seems to have given me the required result but has left me somewhat confused. I thought you needed AS (for Alias) to rename a table. Where exactly is it getting renamed? Is it in the FROM

[Zyppora]

I think you can use or omit AS in the FROM part for MySQL (though I always use it, because it makes the query a bit more readable, to me that is).

The AS part gives the opportunity to use ‘variables’ as references to your tables:

SELECT p.name AS parent_name, c.name AS child_name
FROM parent AS p, child AS c
WHERE c.parent_id = p.object_id

I use p and c as references to resp. the parent and child tables. It gives me the possibility to shorten it (rather than having to write down SELECT parent.name, etc.).

Hope that clears things up a bit.