CODEWITHSUNDEEP
sql
Gareth Maclean
Gareth Maclean

Reputation: 109

SQL Query to check if student1 has a course with student 2

I have one table and I need to check if two users, for whom I have the IDs (e.g. 20 and 21) share the same course, just true or false.

Table: jos_gj_users
Columns: id_user, id_group

Data Example: (20; 4)
              (20; 5)
              (20; 6)
              (21; 6)
              (21; 7)

The data above shows that user 20 and user 21 share the course 6 but how do I get this with SQL just by entering the IDs and without looping through the results with PHP?

Upvotes: 3

Views: 315

Answers (6)

Josh Couts
Josh Couts

Reputation: 1

Give this a try - it accepts the input parameters in the first bolded area, and returns a value of TRUE or FALSE via a CASE statement based on the values in the second bolded areas.

SELECT DISTINCT CASE WHEN
(SELECT DISTINCT COUNT(id_group) FROM jos_gj_users WHERE id_user IN (20, 21)
GROUP BY id_group
HAVING COUNT(DISTINCT id_user) = 2) IS NOT NULL THEN 'TRUE'
ELSE 'FALSE'
END
FROM jos_gj_users

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838066

Try a self-join:

SELECT T1.id_group
FROM jos_gj_users T1
JOIN jos_gj_users T2
ON T1.id_group = T2.id_group
WHERE T1.id_user = 20
AND T2.id_user = 21

To just get a "true or false" result you can check from the client to see if at least one row exists in the result set rather than fetching the entire results.

Alternatively you can do it in SQL by wrapping the above query in another SELECT that uses EXISTS:

SELECT CASE WHEN EXISTS
(
    SELECT T1.id_group
    FROM jos_gj_users T1
    JOIN jos_gj_users T2
    ON T1.id_group = T2.id_group
    WHERE T1.id_user = 20
    AND T2.id_user = 21
) THEN 1 ELSE 0 END AS result

This query returns either 0 (false) or 1 (true).

Upvotes: 3

TarasB
TarasB

Reputation: 2428

This is query that shows users from same groups.

SELECT
  *
FROM
  jos_gj_users T1
  INNER JOIN jos_gj_users T2 ON T1.id_group = T2.id_group

Upvotes: 0

Novikov
Novikov

Reputation: 4489

SELECT COUNT(*) > 0 FROM jos_gj_users WHERE id_user=54321 AND id_group IN ( SELECT id_group FROM jos_gj_users WHERE id_user = 1345 )

Upvotes: 0

ChrisLively
ChrisLively

Reputation: 88044

You could do it with a subselect:

select id_group
from  jos_gj_users
where (id_user = 20)
and id_group in (select id_group from jos_gj_users where id_user = 21)

Upvotes: 0

Hendrik Brummermann
Hendrik Brummermann

Reputation: 8312

The idea is that you have to join the table to itself. In the first half you look for user 1 and in the second half you look for user 2. And of course only those rows that have the same id_group in both half are relevant:

SELECT count(*)
FROM jos_gj_users As j1, jos_gj_users As j2
WHERE j1.id_user = 20 AND j2.id_user = 21
AND j1.id_group = j2.id_group

This will always return one row with one column: The number of shared courses. If it is 0, they don't share any courses.

Upvotes: 0

Related Questions