CODEWITHSUNDEEP
mysqljoin
MarkP
MarkP

Reputation: 2566

Getting data from two tables if they match.

I am trying to get information from two tables in the database: misc.users and misc.course

The misc.users table contains every person that has ever been to the institute whereas the misc.course table only has information about people that are currently enrolled on courses.

How do I get it to show only the users from the misc.users table IF there is an entry for them in the misc.course table.

The misc.users table schema is:

userId
userFName
userLName
userAge
userDateOfBirth
userAllergies
userNotes

and the misc.course schema is

userId
courseId
courseName
courseStart
courseEnd
courseSemGrade
courseEndGrade

Where userId is the same in both tables.

Thanks in advance :)

Upvotes: 1

Views: 36

Answers (3)

uvais
uvais

Reputation: 416

try this:

SELECT DISTINCT(mu.*) FROM misc.users mu 
INNER JOIN misc.course mc ON mu.userId = mc.userId

Upvotes: 0

BeNdErR
BeNdErR

Reputation: 17927

SELECT u.* FROM course c JOIN users u ON c.userId = u.userId

see a working example here: http://sqlfiddle.com/#!2/f98e5/1

Upvotes: 3

Filipe Silva
Filipe Silva

Reputation: 21657

Try doing:

SELECT * 
FROM users u
WHERE EXISTS (SELECT 1 
              FROM course c
              WHERE c.userId = u.userId)

This returns only the users who have at least one record in course table

Upvotes: 0

Related Questions