在两个表的左连接中，从左表中选择所有记录，并从右表中只选择一个与左表匹配的行记录

izj3ouym 于 2021-06-17 发布在 Mysql

关注(0)|答案(3)|浏览(361)

我有两张table问题和答案
我想选所有的数学题目，但只选一个答案。

$this->db->select('*');
    $this->db->from('questions');
    $this->db->join('answers','questions.id = answers.que_id', 'left'); //how to limit answers to 1
    $this->db->where('questions.subject', 'maths');
    return $this->db->get();

mysql Join codeigniter Limit

来源：https://stackoverflow.com/questions/53967106/in-left-join-of-two-table-select-all-records-from-left-table-and-select-only-one

3条答案

按热度按时间

8yoxcaq71#

您的查询应如下所示：

$query = 'select q.*, a1.* 
from questions AS q
    left join answers AS a1
        on a1.id = (select a2.id 
            from answers AS a2 
            where q.id = a2.que_id
            ORDER BY a2.id 
            limit 1
            );';
$query = $this->db->query($query);
$result = $query->result():

赞(0）回复(0）举报 2021-06-18

u2nhd7ah2#

尝试这样的子查询：

$this->db->select('*');
$this->db->from('answers');
$this->db->join('(select * from questions limit 1)','(questions.id = answers.que_id)', 'inner');
$this->db->where('questions.subject', 'maths');
return $this->db->get();

赞(0）回复(0）举报 2021-06-18

aydmsdu93#

使用子查询，
选择问题，（select answer from answers where questions.id=answers.queu id）作为来自questions where questions.subject='math'的答案；
请注意：这不是一个正在运行的sql—请理解

赞(0）回复(0）举报 2021-06-18