[thelist] MySQL query help
Shashank Tripathi
sub at shanx.com
Sun Jun 9 04:10:01 CDT 2002
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
More information about the thelist
mailing list