CODEWITHSUNDEEP
sqlmysql
digitalfoo
digitalfoo

Reputation: 1195

Join 2 tables based on a single table

I can't seem to get the join / query that I need!

Lets say I have 3 tables (trimmed for this post...)

user_courses
(int) user_id
(int) course_id

users
(int) user_id
(txt) name
(txt) access

courses
(int) course_id
(txt) course_desc

What I am trying to do is select select all users with a certain access type that are taking a specific course (course_id)

something like...

SELECT * 
FROM user_courses uc
JOIN users u
ON uc.user_id = u.user_id
JOIN courses c
ON uc.course_id = c.course_id
WHERE u.access = "foobar"

... but working like I want it to :) I can get close, but will have extra users that don't have the correct access type.

Upvotes: 2

Views: 105

Answers (3)

ksogor
ksogor

Reputation: 873

Use inner join.

SELECT * 
  FROM user_courses uc
    INNER JOIN users u
     ON uc.user_id = u.user_id
    LEFT JOIN courses c
     ON uc.course_id = c.course_id
  WHERE u.access = "foobar"

Upvotes: 1

Jens
Jens

Reputation: 3299

Try

...
WHERE ISNULL(u.access, '') = 'foobar'

Not sure if your 'access' field can be null?

Upvotes: 0

jrharshath
jrharshath

Reputation: 26583

perhaps:

select * from users u 
where u.access='foobar' 
    and exists (select 1 from user_courses uc where uc.user_id=u.user_id)

Cheers

Upvotes: 0

Related Questions