涉及到的表
1.c公司
2.cat
---------------------------------------------------------------------
表com的设计如下
CREATE TABLE `c` (
`id` int(10) unsigned NOT NULL auto_increment,
`uid` int(10) unsigned NOT NULL COMMENT '用户ID',
`cid` int(10) unsigned NOT NULL COMMENT '分类的ID',
PRIMARY KEY (`id`)
);------------------------------------------------------------------
CREATE TABLE `cat` (
`id` int(6) unsigned NOT NULL auto_increment,
`fid` int(6) unsigned NOT NULL default '0' COMMENT '父ID',
`tn` varchar(250) NOT NULL,
PRIMARY KEY (`id`)
);------------------------------------------------------------------
用到的SQL语句
SELECT c.*,
sc.tn AS `cname`,
fc.id AS `fid`
FROM c
LEFT JOIN cat sc ON sc.id=c.id
LEFT JOIN cat fc ON sc.fid=fc.id
WHERE 1=1 AND c.uid='1'
LIMIT 0 ,1 ;----------------------------------------------------------------
想要得到的东西
id,
cname,//此公司分类的名字,是二级分类的名字
fid,//此公司属于的分类的父分类的ID
----------------------------------------------------------------
为什么有那个 LIMIT 0 ,1 就不行了,提示#1064错误,将那个LIMIT 0 ,1 去掉就没有这个错误提示,然后也可以有结果,如果没有去掉就没有这个结果。
1.c公司
2.cat
---------------------------------------------------------------------
表com的设计如下
CREATE TABLE `c` (
`id` int(10) unsigned NOT NULL auto_increment,
`uid` int(10) unsigned NOT NULL COMMENT '用户ID',
`cid` int(10) unsigned NOT NULL COMMENT '分类的ID',
PRIMARY KEY (`id`)
);------------------------------------------------------------------
CREATE TABLE `cat` (
`id` int(6) unsigned NOT NULL auto_increment,
`fid` int(6) unsigned NOT NULL default '0' COMMENT '父ID',
`tn` varchar(250) NOT NULL,
PRIMARY KEY (`id`)
);------------------------------------------------------------------
用到的SQL语句
SELECT c.*,
sc.tn AS `cname`,
fc.id AS `fid`
FROM c
LEFT JOIN cat sc ON sc.id=c.id
LEFT JOIN cat fc ON sc.fid=fc.id
WHERE 1=1 AND c.uid='1'
LIMIT 0 ,1 ;----------------------------------------------------------------
想要得到的东西
id,
cname,//此公司分类的名字,是二级分类的名字
fid,//此公司属于的分类的父分类的ID
----------------------------------------------------------------
为什么有那个 LIMIT 0 ,1 就不行了,提示#1064错误,将那个LIMIT 0 ,1 去掉就没有这个错误提示,然后也可以有结果,如果没有去掉就没有这个结果。
返回空记录集
sc.tn AS `cname`,
fc.id AS `fid`
FROM c
LEFT JOIN cat sc ON sc.id=c.id
LEFT JOIN cat fc ON sc.fid=fc.id
WHERE 1=1 AND c.uid='1'有无结果
-> `id` int(10) unsigned NOT NULL auto_increment,
-> `uid` int(10) unsigned NOT NULL COMMENT '用户ID',
-> `cid` int(10) unsigned NOT NULL COMMENT '分类的ID',
-> PRIMARY KEY (`id`)
-> );
Query OK, 0 rows affected (0.50 sec)mysql> CREATE TABLE `cat` (
-> `id` int(6) unsigned NOT NULL auto_increment,
-> `fid` int(6) unsigned NOT NULL default '0' COMMENT '父ID',
-> `tn` varchar(250) NOT NULL,
-> PRIMARY KEY (`id`)
-> );
Query OK, 0 rows affected (0.09 sec)mysql> SELECT c.*,
-> sc.tn AS `cname`,
-> fc.id AS `fid`
-> FROM c
-> LEFT JOIN cat sc ON sc.id=c.id
-> LEFT JOIN cat fc ON sc.fid=fc.id
-> WHERE 1=1 AND c.uid='1'
-> LIMIT 0 ,1 ;
Empty set (0.08 sec)mysql> select version();
+----------------------+
| version() |
+----------------------+
| 5.1.33-community-log |
+----------------------+
1 row in set (0.00 sec)mysql>