How to select multiple rows of mysql table as one row

Question

I have a participants table as shown below

MemberID    Name     BookID
    1       ABC         10
    1       ABC         14
    2       XYZ         10
    3       PQR         14

I want to choose MemberIDwhich contains both BOOKID 10 and 14in one line.

Expected Result:

MemberID
1

I tried the code below, but it does not work:

select MemberID from member where BookID IN (10,14)

Answer 1

You can try the following:

SELECT MemberID, GROUP_CONCAT(BookID) AS BookID
FROM member
GROUP BY MemberID
HAVING FIND_IN_SET(10, BookID) > 0 AND FIND_IN_SET(14, BookID) > 0

Here SQLFIDDLE.

Another solution may use JOINas follows:

SELECT x.MemberID
FROM member x
INNER JOIN member y ON x.MemberID = y.MemberID
  AND x.BookID = 10
  AND y.BookID = 14

Here SQLFIDDLE.

Answer 2

You can use the DISTINCTkeyword

 select distinct MemberID from member where BookID

Answer 3

, MemberID. -

SELECT  DISTINCT MemberID FROM member
WHERE BookID IN (10,14)

, 10 12.