left right inner Join 多表联查的问题(附实际例子)!

[ImFantasy]

sql="select * FROM phpcms_content AS a left JOIN phpcms_attachment AS b ON 'a.contentid' = 'b.contentid' ORDER BY a.contentid DESC limit 0 1"

查询返回是:
Array
(
[contentid] =>
[catid] =>
[typeid] => 0
[areaid] => 0
[title] => 寂寞的迷途小羔羊
[style] =>
[thumb] => uploadfile/2009/0815/20090815111022437.jpg
[keywords] => 迷途 羔羊
[description] =>
[posids] => 0
[url] => suoai/w810/zhuti-2.html
[listorder] =>
[status] => 99
[userid] =>
[username] => seeyou
[inputtime] => 1250305989
[updatetime] => 1250327706
[searchid] => 2
[islink] => 0
[prefix] =>

[aid] =>
[module] =>
[field] =>
[filename] =>
[filepath] =>
[filetype] =>
[filesize] =>
[fileext] =>
[isimage] =>
[isthumb] =>
[downloads] =>
[uploadtime] =>
[uploadip] =>
)
phpcms_attachment 这个表里面的内容一个都没有值



sql="select * FROM phpcms_content AS a right JOIN phpcms_attachment AS b ON 'a.contentid' = 'b.contentid' ORDER BY a.contentid DESC limit 0 1"

查询返回的内容是:
Array
(
[contentid] => 0
[catid] => 0
[typeid] =>
[areaid] =>
[title] =>
[style] =>
[thumb] =>
[keywords] =>
[description] =>
[posids] =>
[url] =>
[listorder] => 0
[status] =>
[userid] => 1
[username] =>
[inputtime] =>
[updatetime] =>
[searchid] =>
[islink] =>
[prefix] =>
[aid] => 2
[module] => phpcms
[field] => uploadfile
[filename] => w800.thm
[filepath] => 2009/0815/20090815095533242.thm
[filetype] => application/octet-stream
[filesize] => 440320
[fileext] => thm
[isimage] => 0
[isthumb] => 0
[downloads] => 0
[uploadtime] => 1250301333
[uploadip] => 113.96.45.196
)
phpcms_content表的内容都没有值


sql="select * FROM phpcms_content AS a inner JOIN phpcms_attachment AS b ON 'a.contentid' = 'b.contentid' ORDER BY a.contentid DESC limit 0 1"

返回的内容为空.


下面是数据结构
phpcms_content
Array
(
[contentid] => 2
[catid] => 44
[typeid] => 0
[areaid] => 0
[title] => 寂寞的迷途小羔羊
[style] =>
[thumb] => uploadfile/2009/0815/20090815111022437.jpg
[keywords] => 迷途 羔羊
[description] => 寂寞的迷途小羔羊
[posids] => 0
[url] => suoai/w810/zhuti-2.html
[listorder] => 0
[status] => 99
[userid] => 1
[username] => seeyou
[inputtime] => 1250305989
[updatetime] => 1250327706
[searchid] => 2
[islink] => 0
[prefix] =>
)

phpcms_attachment
Array
(
[aid] => 7
[module] => phpcms
[catid] => 0
[contentid] => 2
[field] => thumb
[filename] => w810.jpg
[filepath] => 2009/0815/20090815111022437.jpg
[filetype] => image/pjpeg
[filesize] => 10629
[fileext] => jpg
[description] =>
[isimage] => 1
[isthumb] => 0
[downloads] => 0
[listorder] => 0
[userid] => 1
[uploadtime] => 1250305822
[uploadip] => 113.96.45.196
)



请问我如何才能联合查询到这个id 在两个表里面所有的内容,返回一个数组呢?


如果两个表可以,三个表一起查,又如何做呢?

[ImFantasy]

select * FROM phpcms_content AS a full JOIN phpcms_attachment AS b ON 'a.contentid' = 'b.contentid' ORDER BY a.contentid DESC LIMIT 1

这样查询, 报错:


MySQL Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full JOIN phpcms_attachment AS b ON 'a.contentid' = 'b.contentid' ORDER BY a.con' at line 1
MySQL Errno : 1064

[ImFantasy]

select * FROM phpcms_content AS a full JOIN phpcms_attachment AS b ON 'a.contentid' = 'b.contentid' ORDER BY a.contentid DESC LIMIT 1

这样查询, 报错:


MySQL Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full JOIN phpcms_attachment AS b ON 'a.contentid' = 'b.contentid' ORDER BY a.con' at line 1
MySQL Errno : 1064

[ImFantasy]

MySQL Query : select * FROM phpcms_content AS a FULL OUTER JOIN phpcms_attachment AS b ON 'a.contentid' = 'b.contentid' ORDER BY a.contentid DESC LIMIT 1
MySQL Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN phpcms_attachment AS b ON 'a.contentid' = 'b.contentid' ORDER B' at line 1

[ACMAIN_CHM]

MySQL 中没有 full join

按照你的标题直接用 left join 就行了。

[ImFantasy]

left join phpcms_attachment 这个表里面的内容一个都没有值


我要phpcms_attachment表里面的一个字段啊?

[ACMAIN_CHM]

建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。

参考一下个人觉得比较好的提问方式。得到回复的机率会大一些

http://www.accessbbs.cn/bbs/viewthread.php?tid=19552
http://www.accessbbs.cn/bbs/viewthread.php?tid=19546

[ImFantasy]

这还不够详细吗? 楼上的看看 实例啊, 数据库结构都 select出来 打印成数组了啊.

我现在就想要 同时返回两个数据库的内容. left Join 出来只有一个数据库的

[ACMAIN_CHM]

为什么不直接贴出你的 create table 语句？ 和数据？

这样别人可以直接在本机创建表，然后测试。当然你现在这样也可以。 我自己来猜你的数据类型（文本还是数字，哪个字段是主键？）然后猜着建表。之后猜你期望的正确结果

[ImFantasy]

哎. 用得着 你关心他是什么数据类型吗. 就是select 关联查询了, 又不是其他的, 你愿意帮忙,就帮吧. 不愿意, 不用在这里说风凉话.

[ImFantasy]

自己已经搞定. 不需要 什么 Join


select * from phpcms_content c,phpcms_content_count n where c.contentid=n.contentid and status=99 order by n.hits_week desc

完事.