Bdtjtk

Assume i have the following table, 'some_table', which looks like this,

email | friend_email |

----------------------

s1    | f1           |

----------------------

s2    | f1           |

----------------------

s1    | f2           |

----------------------

s2    | f2           |

----------------------

s3    | f2           |

----------------------

s1    | f3           |

----------------------

s2    | f3           |

----------------------

s4    | f3           |

I then want to find the distinct and common values under 'email' between f1, f2 and f3.
The result returned should only be s1 and s2.

The SQL statement used will look like this,

SELECT DISTINCT email FROM some_table WHERE friend_email IN ('f1') AND email in (SELECT DISTINCT email FROM some_table WHERE friend_email IN ('f2')) AND email in (SELECT DISTINCT email FROM some_table WHERE friend_email IN ('f3'));

The problem with this is it will get very lengthy when i have more friend_emails to add in.

In my node.js code, i want to use '?' (i think they are called constants ?) in the sql statements but i haven't succeeded. A sample of the code is below,

var getCommonFriends = "SELECT DISTINCT email FROM some_table WHERE friend_email = '?'";

var getCommonFriendsAppend = " AND email in (SELECT DISTINCT email FROM some_table WHERE friend_email = '?')";



var friends = ["f1", "f2", "f3"];



var combinedSqlStatement = getCommonFriends;

var totalFriends = friends.length;

for(var i = 0; i < totalFriends; i++){

    combinedSqlStatement = combinedSqlStatement + getCommonFriendsAppend;

}

My question is how do i come up with an sql statement that does the above and uses constants aka '?'

I think you might be able to do a simple aggregation query here:

SELECT email

FROM some_table

WHERE friend_email IN ('f1', 'f2', 'f3')

GROUP BY email

HAVING COUNT(DISTINCT friend_email) = 3;

This option aggregates by email item, and then asserts that a certain set of friends are all present/associated with that item. This solution scales well, because to expand to more friends, you only need to add another friend to the IN clause, and change the distinct count assertion at the end of the query.

Demo

Here is a version of the query which might be more friendly in Node JS:

SELECT email

FROM some_table

WHERE friend_email IN

(

    SELECT ? FROM dual UNION ALL

    SELECT ? FROM dual UNION ALL

    SELECT ? FROM dual

)

GROUP BY email

HAVING COUNT(DISTINCT friend_email) = 3;

Then, bind the literal values 'f1', 'f2', and 'f3' to the above query.

Demo