如何从2个不同的表中获取数据？

Question

我有2个表User_Posts

CREATE TABLE IF NOT EXISTS `User_Posts` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `user_id` int(12) NOT NULL, 
    `wall_id` int(12) NOT NULL, 
    `text` text COLLATE utf8_bin NOT NULL, 
    `img` varchar(255) COLLATE utf8_bin NOT NULL, 
    `time` date NOT NULL 
    PRIMARY KEY (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=7 ; 

User_Activity

CREATE TABLE IF NOT EXISTS `User_Activity` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `content` varchar(55) COLLATE utf8_bin NOT NULL, 
    `activity_id` int(12) NOT NULL, 
    `activity` varchar(88) COLLATE utf8_bin NOT NULL, 
    `user_id` int(12) NOT NULL, 
    `time` date NOT NULL, 
    `value` varchar(12) COLLATE utf8_bin NOT NULL, 
    PRIMARY KEY (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=131 ; 

，我想获得的所有活动和岗位，其中USER_ID =？

试图与

$act = $this->app->db->rawQuery("SELECT p.*, a.* 
           FROM User_Posts p 
           JOIN User_Activity a 
           ON a.user_id = ? OR p.user_id = ?", array($id,$id)); 

做我得到的数据是这样

Array 
(
    [0] => Array 
     (
      [id] => 130 
      [user_id] => 16 
      [wall_id] => 0 
      [text] => 'Text' 
      [img] => 0 
      [time] => 2014-10-31 
      [deleted] => 0 
      [content] => movie 
      [activity_id] => 5 
      [activity] => favorite 
      [value] => 0 
     ) 

    [1] => Array 
     (
      [id] => 130 
      [user_id] => 16 
      [wall_id] => 0 
      [text] => 'Text' 
      [img] => /posts/ceee9b387dcf72bcb19d1c1c73147ef3.jpg 
      [time] => 2014-10-31 
      [deleted] => 0 
      [content] => movie 
      [activity_id] => 5 
      [activity] => favorite 
      [value] => 0 
     ) 

) 

但我需要

Array 
(
    [0] => Array 
     (
      [id] => 1 
      [user_id] => 16 
      [wall_id] => 0 
      [text] => 'Text' 
      [img] => 0 
      [time] => 2014-10-31 
     ) 

    [1] => Array 
     (
      [id] => 2 
      [user_id] => 16 
      [wall_id] => 0 
      [text] => 'Text' 
      [img] => /posts/ceee9b387dcf72bcb19d1c1c73147ef3.jpg 
      [time] => 2014-10-31 
     ) 

     [2] => Array 
     (
      [id] => 130 
      [content] => movie 
      [activity_id] => 5 
      [activity] => favorite 
      [value] => 0 
     ) 

) 

响应与附加活动的职位，但我需要不同每个人的结果

该怎么办？

Answer 1

只是一个小改

$act = $this->app->db->rawQuery("SELECT p.*, a.* 
          FROM User_Posts p, User_Activity a 
          WHERE a.user_id = p.user_id 
          AND p.user_id = ?", array($id)); 

Answer 2

你不需要JOIN关键字。

SELECT * 
FROM User_Posts, User_Activity 
WHERE `User_Activity`.`User_id` = `User_Posts`.`user_id`