how to select a id or a value that is common to multiple values in a column

Question

this is my problem

book student_id
1     1
2     2
3     3
1     2

in this case i want book 1 since it is common to the combination of student 1 and 2(the number of students to match may change)

how can in get the book that is common to two or more students based on the combination.is there any way to make these values act like temporary columns

score 0 · Answer 1 · answered Mar 31 '15 at 13:32

0

You just use group by with having:

select book
from table t
group by book
having count(*) > 1;

If you can have duplicates in the table (same student and same book), then the last line should be:

having count(distinct student_id) > 1;

answered Mar 31 '15 at 13:32

Gordon Linoff

1,242,037
58
646
786

thank you for the response but this is not the case .think of an imaginary function where I only pass student ids and get the book id that belong to that combination a combination have only one book.and you cant add a grouping column – lk404 Mar 31 '15 at 13:43

how to select a id or a value that is common to multiple values in a column

1 Answers1