Allen Allen - 1 month ago 5
SQL Question

SQL Query but Eliminate Some Records based on another Query

I can loop through the table produced by the following query

Select userid_fk as id, courseID_fk
from tbl_userCourse
inner join tbl_users
on user_id=userid_fk
where courseID_fk=5


and then get rid of some records where the following sql results in empty (I have to pass in the $user_id). I can do this on a web page using php using this query.

Select min(schDate) as min
from tbl_schedule
where userid_fk=".$user_id."
and schDate>getDate()
and courseID_fk=5


But now I'm wondering if I can combine these two sql statement somehow and just use one query statement. If so, how would I do this?

Answer

Use Exists

Select userid_fk as id, courseID_fk 
from tbl_userCourse 
inner join tbl_users 
        on user_id=userid_fk 
where courseID_fk=5
and exists (
    Select * 
    from tbl_schedule 
    where tbl_schedule.userid_fk = tbl_userCourse.userid_fk
      and tbl_schedule.schDate>getDate()  
      and tbl_schedule.courseID_fk=5
)

Exists returns true if the query between braces has at least one record, so and exits (...) will "skip" those in which that subquery is empty.

As you see this query is quite readable as it resembles what you explained you want

Comments