Selecting from the same table more than once in th

问题:

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 ?

回答1:

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')

回答2:

You need to give multiple joins to the same table a distinguishing reference. i.e.

SELECT * FROM exhibit LEFT JOIN stand AS a ON exhibit.ex_pref_one = stand.stand_id LEFT JOIN stand AS b ON exhibit.ex_pref_two = stand.stand_id

Then refer to the results using those references - you'll see what I mean from the results you get from such a query.