在同一查询中多次选择同一个表 [英] Selecting from the same table more than once in the same query

问题描述

我有一个名为展览的表格，其中包含：

• ex_id


• ex_inv_id


• 
  
• 
  
• ex_pref_one


• ex_pref_two


• ex_pref_three


• ex_pref_four


• ex_terms_conditions


• ex_pref_one_approved


• ex_pref_two_approved


• ex_pref_three_approved


• ex_pref_four_approved

p>

• stand_id


• stand_no


• stand_type

我需要通过从展览表中选择ex_pref_one / two / three / four列= stand_id

我尝试使用连接，但我有一个错误，说表 stand 不存在
我使用codeigniter PHP框架

这里的代码很抱歉我没有发布它第一

  $ user_id = $ this-> session-> userdata（'user_id'）; 
 if（！$ user_id || $ user_id == 0）return 0; 
 
 $ where = array（
'exhibit.ex_user_id'=> $ user_id，
）; 
 return $ this-> db-> select（'
 shows。*，
 S1。*，
 S2。*，
 S3。*，
 S4。*，
'）
  - > join（'stand AS S1'，'exhibit.ex_pref_one = S1.stand_id'，'LEFT'）
  - > join （'stand AS S2'，'exhibit.ex_pref_two = S2.stand_id'，'LEFT'）
  - > join（'stand AS S3'，'exhibit.ex_pref_three = S3.stand_id'，'LEFT'） 
  - > join（'stand AS S4'，'exhibit.ex_pref_four = S4.stand_id'，'LEFT'）
  - >其中（$ where）
  - > from 'exhibit'）
  - > get（）
  - > result（）; 
 

$ this-> db-> last_query（）;

的结果

  SELECT`exhibits`。*，
`S1`。*，
`S2`。 。*，
`S4`。* 
 FROM（`exhibit`）
 LEFT JOIN`stand` AS S1 ON`exhibit`.`ex_pref_one` =`S1`.`stand_id` 
 LEFT JOIN`stand` AS S2 ON` exhibit`.`ex_pref_two` =`S2`.`stand_id` 
 LEFT JOIN`stand` AS S3 ON` exhibit`.exex_refree_ = s3`。 `stand_id` 
 LEFT JOIN`stand` AS S4 ON` exhibit`.`ex_pref_four` =`S4`.`stand_id` 
 WHERE`exhibits`.`ex_user_id` = 1 
  

结果的var_dump

  array（size = 1）
 
 0 => 
 
 object（stdClass）[25] 
 public'ex_id'=> string'2'（length = 1）
 public'ex_inv_id'=> string'2147483647'（length = 10）
 public'ex_user_id'=> string'1'（length = 1）
 public'ex_pref_one'=> string'6'（length = 1）
 public'ex_pref_two'=> string'14'（length = 2）
 public'ex_pref_three'=> string'13'（length = 2）
 public'ex_pref_four'=> string'21'（length = 2）
 public'ex_terms_conditions'=> string'1'（length = 1）
 public'ex_pref_one_approved'=> string'0'（length = 1）
 public'ex_pref_two_approved'=> string'0'（length = 1）
 public'ex_pref_three_approved'=> string'0'（length = 1）
 public'ex_pref_four_approved'=> string'0'（length = 1）
 public'stand_id'=> string'21'（length = 2）
 public'stand_no'=> string'20'（length = 2）
 public'stand_type'=> string'Gold'（length = 4）
  

但是它似乎只是通过ex_pref_four的其他为什么？

解决方案

首先是别名，您不需要 AS 。所以你可以这样做：

  $ this-> db-> select（'
 exhibit。 *，
 S1。*，
 S2。*，
 S3。*，
 S4。*，
'）
  - > from 
  - > join（'stand S2'，'exhibit.ex_pref_two ='）
  - > join（'stand S1'，'exhibit.ex_pref_one = S1.stand_id'，'LEFT' 
  - > join（'stand S3'，'exhibit.ex_pref_three = S3.stand_id'，'LEFT'）
  - ，'exhibit.ex_pref_four = S4.stand_id'，'LEFT'）
  - > where（'whatever'）
  




I have a table called exhibit which contains:

• ex_id
• ex_inv_id
• ex_user_id
• ex_pref_one
• ex_pref_two
• ex_pref_three
• ex_pref_four
• ex_terms_conditions
• ex_pref_one_approved
• ex_pref_two_approved
• ex_pref_three_approved
• ex_pref_four_approved

And another table called stand which contains:

• stand_id
• stand_no
• stand_type

I need to put the values in a datatable now by selecting from the exhibit table where the ex_pref_one/two/three/four columns = stand_id

I tried using a join but i got an error that said the table stand does not exist I am using the codeigniter PHP framework

Here is the Code Sorry i didnt post it first

        $user_id = $this->session->userdata('user_id');
    if(!$user_id || $user_id == 0 )return 0;

    $where = array(
        'exhibit.ex_user_id'=>$user_id,
        );
    return $this->db->select('
        exhibit.*,
        S1.*,
        S2.*,
        S3.*,
        S4.*,
        ')
    ->join('stand AS S1', 'exhibit.ex_pref_one = S1.stand_id', 'LEFT')
    ->join('stand AS S2', 'exhibit.ex_pref_two = S2.stand_id', 'LEFT')
    ->join('stand AS S3', 'exhibit.ex_pref_three = S3.stand_id', 'LEFT')
    ->join('stand AS S4', 'exhibit.ex_pref_four = S4.stand_id', 'LEFT')
    ->where($where)
    ->from('exhibit')
    ->get()
    ->result();

Result of $this->db->last_query();

    SELECT `exhibit`.*,
       `S1`.*,
       `S2`.*,
       `S3`.*,
       `S4`.*
FROM (`exhibit`)
LEFT JOIN `stand` AS S1 ON `exhibit`.`ex_pref_one` = `S1`.`stand_id`
LEFT JOIN `stand` AS S2 ON `exhibit`.`ex_pref_two` = `S2`.`stand_id`
LEFT JOIN `stand` AS S3 ON `exhibit`.`ex_pref_three` = `S3`.`stand_id`
LEFT JOIN `stand` AS S4 ON `exhibit`.`ex_pref_four` = `S4`.`stand_id`
WHERE `exhibit`.`ex_user_id` = 1

var_dump of the result

array (size=1)

 0 => 

 object(stdClass)[25]
  public 'ex_id' => string '2' (length=1)
  public 'ex_inv_id' => string '2147483647' (length=10)
  public 'ex_user_id' => string '1' (length=1)
  public 'ex_pref_one' => string '6' (length=1)
  public 'ex_pref_two' => string '14' (length=2)
  public 'ex_pref_three' => string '13' (length=2)
  public 'ex_pref_four' => string '21' (length=2)
  public 'ex_terms_conditions' => string '1' (length=1)
  public 'ex_pref_one_approved' => string '0' (length=1)
  public 'ex_pref_two_approved' => string '0' (length=1)
  public 'ex_pref_three_approved' => string '0' (length=1)
  public 'ex_pref_four_approved' => string '0' (length=1)
  public 'stand_id' => string '21' (length=2)
  public 'stand_no' => string '20' (length=2)
  public 'stand_type' => string 'Gold' (length=4)

But it seems that it only pulls the ex_pref_four through and none of the others Why is that ?

解决方案

First to alias you don't need AS. so you can do it like this:

$this->db->select('
        exhibit.*,
        S1.*,
        S2.*,
        S3.*,
        S4.*,
        ')
    ->from('exhibit')
    ->join('stand S1', 'exhibit.ex_pref_one = S1.stand_id', 'LEFT')
    ->join('stand S2', 'exhibit.ex_pref_two = S2.stand_id', 'LEFT')
    ->join('stand S3', 'exhibit.ex_pref_three = S3.stand_id', 'LEFT')
    ->join('stand S4', 'exhibit.ex_pref_four = S4.stand_id', 'LEFT')
    ->where('whatever')

这篇关于在同一查询中多次选择同一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！