I have member table like below
MemberID Name BookID
1 ABC 10
1 ABC 14
2 XYZ 10
3 PQR 14
I want to select a MemberID that contains both the BOOKID 10 and 14 in single row.
Expected Output:
MemberID
1
I have tried below code but it doesnt work:
select MemberID from member where BookID IN (10,14)
You can try like this:
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 is the SQLFIDDLE.
Another solution can be using JOIN like this:
SELECT x.MemberID
FROM member x
INNER JOIN member y ON x.MemberID = y.MemberID
AND x.BookID = 10
AND y.BookID = 14
Here is the SQLFIDDLE.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments