[thelist] MySQL query help

[Shashank Tripathi]

Kevin,

Below is what I have. All raw output from MySQL dos prompt, I believe
the table structure etc is similar to yours and the SQL I have at the
end (similar to rudy's btw) is working.


******************** START ************************

mysql> show tables

+-----------------+
| Tables_in_mysql |
+-----------------+
| categories      |
| status          |
| tasks           |
+-----------------+
3 rows in set (0.01 sec)


mysql> desc categories;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     |      |     | 0       |       |
| categoryname | varchar(24) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)


mysql> desc status;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     |      |     | 0       |       |
| sectiontitle | varchar(24) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


mysql> desc tasks;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| id       | int(11) |      |     | 0       |       |
| catid    | int(11) |      |     | 0       |       |
| statusid | int(11) |      |     | 0       |       |
+----------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)


mysql> select * from categories;
+----+--------------+
| id | categoryname |
+----+--------------+
|  1 | Category 1   |
|  2 | Category 2   |
+----+--------------+
2 rows in set (0.00 sec)


mysql> select * from status;
+----+--------------+
| id | sectiontitle |
+----+--------------+
|  1 | Deleted      |
|  2 | Ready        |
|  3 | Cancelled    |
+----+--------------+
3 rows in set (0.00 sec)


mysql> select * from tasks;
+----+-------+----------+
| id | catid | statusid |
+----+-------+----------+
|  1 |     1 |        2 |
|  2 |     1 |        2 |
|  3 |     1 |        1 |
|  4 |     2 |        3 |
|  5 |     2 |        1 |
+----+-------+----------+
5 rows in set (0.00 sec)


mysql> select
    ->     tasks.id
    ->     ,categories.categoryname
    ->     ,status.sectiontitle
    -> from
    ->     tasks, categories, status
    -> where
    ->     tasks.catid = categories.id
    ->  and tasks.statusid = status.id
    -> order by
    ->     tasks.id
    -> ;

+----+--------------+--------------+
| id | categoryname | sectiontitle |
+----+--------------+--------------+
|  1 | Category 1   | Ready        |
|  2 | Category 1   | Ready        |
|  3 | Category 1   | Deleted      |
|  4 | Category 2   | Cancelled    |
|  5 | Category 2   | Deleted      |
+----+--------------+--------------+
5 rows in set (0.01 sec)


******************** START ************************


Hope this is what you were looking for. Holler over if it isn't and will
look into it.

Cheers,
Shashank