dongyuli4538
dongyuli4538
2015-01-03 22:44

替换连接3个表而不重复

已采纳

I have 3 tables in mysql, table A, B and C.
C has a relationship with B (b_id) and B has a relationship with A (a_id).

that means A has many B, and B has many C. To select all, I have this query:

SELECT a.id, a.name, a.text, b.ptext, c.ctext
FROM tableA as a
JOIN 
  tableB as b 
ON 
  (b.p_id = a.id)
JOIN 
  tableC as c 
ON 
  (c.p_pid = b.pid)
WHERE a.id = 1

which returns this:

| ID |  NAME |         TEXT |  BTEXT |  CTEXT |
|----|-------|--------------|--------|--------|
|  1 | page1 | futkdvthsa_1 | post_1 | thing1 |
|  1 | page1 | futkdvthsa_1 | post_2 | thing2 |
|  1 | page1 | futkdvthsa_1 | post_2 | thing3 |

is there any chance of getting something like this:

id |  name |         text | posts
 1 | page1 | futkdvthsa_1 | posts (post_1 = ( thing1 ), post_2 = ( thing2, thing3 ) )

Sigle queries for each table or would need to do in a php way?

P.S. this would be for a wordpress plugin with custom tables.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • douchunji1885 douchunji1885 6年前

    I guess the simplest way to do this is by doing it with php. You would have to use a stored procedure to concatenate your fields with sql.

    点赞 评论 复制链接分享