dongyikong6207
2015-07-02 10:44 阅读 209
已采纳

具有多个表连接的mysql查询,用于从一个表中获取排除匹配条件的数据

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:

    40,

    41,

    42

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



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

 ->group_by('sessionschedule.id')->find_all();
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

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

    Try this:

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

相关推荐