CODEWITHSUNDEEP
mysql
gtilflm
gtilflm

Reputation: 1465

MySQL JOIN Statement with Two Conditions

I have two tables both with columns called member_id. I'd like to select all member_ids...

  1. WHERE end_date <= NOW() from table 1, and
  2. approved='n' from table 2.

I'm very unfamiliar with JOIN, but I believe this is what's needed here. Something like this doesn't seem to be working though...

SELECT both.member_id, both.course_id 
FROM vbc_status 
INNER JOIN course_enrollment ON both.member_id=both.member_id AND 
                                both.end_date <= NOW() AND 
                                both.approved='n'

How can I make this happen?

Upvotes: 0

Views: 43

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

I think you want this:

SELECT *
FROM vbc_status v 
INNER JOIN course_enrollment c ON v.member_id = c.member_id 
WHERE v.end_date <= NOW() AND c.approved='n'

Upvotes: 1

Chris
Chris

Reputation: 984

SELECT t1.member_id, t1.course_id FROM vbc_status t1
    JOIN course_enrollment ce USING(member_id)
    WHERE t1.end_date <= NOW() AND ce.approved='n'

I assumed end_date is a column of vbc_status and approved is a column of course_enrollment.

Explanation of the statement: You set up table aliases with vbc_status t1, this makes t1 the alias for the table vbc_status, and course_enrollment gets the alias ce. Then on selecting you have to prefix the fields you want to see with the alias of the table, and the same goes for the where statement.

Upvotes: 0

shawnt00
shawnt00

Reputation: 17915

You still have to reference the original columns by their source even after the join. I'm not sure which table has course_id so you'll have to fix that one before it will run.

SELECT vbc_status.member_id, ?.course_id
FROM vbc_status INNER JOIN course_enrollment
    ON  vbc_status.member_id = course_enrollment.member_id 
        AND vbc_status.end_date <= NOW() AND course_enrollment.approved = 'n'

You'd probably find table aliases to be handy.

Upvotes: 1

Related Questions