2015-07-02 10:44 阅读 209


Looking for some help on mysql query for joining table to extract the data.

I have two main tables,

  1. session_schedule -> all schedules here entries:




  2. session_booking. -> all enrollments here with student_id

    40 - me

    40 - my friend

    41 - my friend

    42 - none

When a user enrolls for a schedule from the session_schedule, this gets populated in the session_booking table. Now I want to fetch all schedules which are not enrolled by me.

Please see the following code.

This should address the following cases:

  1. sessions not enrolled by anyone.

  2. sessions enrolled by others , but not by me.

Its failing the second case, where its picking up session enrolled by me and others.

I am getting all three entries.. 40, 41, 42.
I should not be getting 40

Please see the following code.

 $session_schedule = ORM::factory('sessionschedule')->select(
    'sessionschedule.session_id' ,
    'sessions_booking.session_id' ,
    'sessions_booking.student_id', ) 

 // this join is to exclude already paid sessions by the user                   
 ->where('sessionschedule.session_id', '=', $_POST['id'] )
 ->where('sessions_booking.session_id','=',NULL) // for sessions nobody booked                                          
 ->or_where('sessions_booking.student_id','!=',$user->id  )// for sessions booked by others             

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

1条回答 默认 最新

  • 已采纳
    duanli8577 duanli8577 2015-07-02 10:55

    Try this:

    SELECT ses.* 
    FROM session_schedule ses 
    LEFT JOIN session_booking sb ON AND sb.student_id='me' 
    WHERE sb.session_id IS NULL;
    点赞 评论 复制链接分享