Mysql query to fetch data

Question

i have a table "request" with 4 columns namely:

1.recId :long primary key
2.interactionId:long
3.requestedBy:boolean
4.requestedType:boolean

and data is as follows: VALUES

(185,455699,0,5),
(186,455746,0,1),
(187,455746,1,1),
(188,455752,0,1),
(189,455753,0,1),
(190,455753,1,1),
(191,455754,1,1)

i want a query to fetch all the rows where interactionId is same and having requestedBy both 1 and 0 values and requestType=1;

regards, Nihar

Answer 1

Your question was a little difficult to understand. I'm assuming you meant this:

I want a query to fetch all pairs of rows from the request table where:

• the interactionId is same in both rows
• requestType = 1 for both rows
• requestedBy is 1 in one row and 0 in the other row

If so, then try this:

SELECT *
FROM request T1
JOIN request T2
ON T1.interactionId = T2.interactionId
AND T1.requestType = 1
AND T2.requestType = 1
AND T1.requestedBy = 0
AND T2.requestedBy = 1