CODEWITHSUNDEEP
mysql
Recur
Recur

Reputation: 1428

How do you select a group of rows, in a table, based on if the user is in that group

Given this table, I want to be able to grab all the rows of a certain course_id if the user is part of that course_id

course_id    user
---------    -----
        1        a
        1        b
        1        c
        2        a
        2        c
        3        a
        3        b
        3        c
        3        d

I was thinking of getting the information and using php to parse it but I'd like to learn the proper way to do this in sql.

I do apologize if this has been asked already.. I can't quite figure out how to ask this question clearly nor search it with the right wording.

Edit 1: As an example, I want to get the courses that user b is in so the example output would be:

course_id    user
---------    ----
        1       a
        1       b
        1       c
        3       a
        3       b
        3       c
        3       d

Upvotes: 0

Views: 35

Answers (4)

Ian McLaird
Ian McLaird

Reputation: 5585

Try this.

SELECT course_id, user FROM <table> WHERE course_id IN 
    (SELECT course_id FROM <table> WHERE user = 'b');

Upvotes: 0

avk
avk

Reputation: 871

Suppose you want all the rows with the same course ID as user D, it would look something like this:

Select course_id, user From yourTable Where course_id in ( select distinct course_id from yourTable where user ='d' )

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I would approach this using an exists clause:

select t.*
from thistable t
where exists (select 1
              from thistable t2
              where t2.course_id = t.course_id and t2.user = <userid>
             );

The subquery says: any row in the table will be included if there is another row in the table that has the specified user.

Upvotes: 0

midan888
midan888

Reputation: 179

Try this one. might help, replace <table_name> with your table

SELECT user FROM <table_name> WHERE course_id = (SELECT course_id FROM <table_name> WHERE user = 'a' LIMIT 1);

Upvotes: 0

Related Questions