Using SQL join and subquery to query two tables in R

Question

I'm new at this.

I have two .txt files and I use R with sqldf pakage to request them

The first table (venues.txt) looks like this:

userID,venueID,year,month,date,hour 1302,47,2012,2,24,11 45,132,2012,2,24,11 24844,86,2012,2,24,11 896,248,2012,2,24,11 5020,29,2012,2,24,11 

The second table (friends.txt) looks like this:

 userID,friendID 1,5 1,9 1,50 1,102 1,300 

I want to request places (siteID) that a user visited (say userID = 1) with one or more of his friends (friendID)

Note. The userID, friendID of friends can be associated with the user ID in the places table.

query results should look like this:

 venueID friendID 47 5 47 9 29 102 86 102 

I can do this using many separate queries and then attach them to the table, but my dataset is very large. Is there an easier way to do this?

I managed to request all the places that the user or his friends visited:

 sqldf("select userID, venueID from data where userID=1 OR userID IN (select friendID from freind where userID=1)") 

Many thanks.

Answer 1

I am a Java pl / sql developer, so I want to answer: “a list of places that at least two friends visited” using only the connection and assuming that the data from venues.txt is called places and the friends.txt file is called a friend in the FROM clause. Basically, I assume that these files are tables.

 SELECT v1.venueID, f.friendID FROM venues v1 INNER JOIN friends f ON v1.userID = f.userID INNER JOIN venues v2 ON v2.userID = f.friendID WHERE v1.venueID = v2.venueID 

and if you want to add additional conditions, that is, "at least two friends whom you visited together, therefore having the same year, month, date, hour," just add them to the filter (WHERE clause). Thus, the request will look like this:

 SELECT v1.venueID, f.friendID FROM venues v1 INNER JOIN friends f ON v1.userID = f.userID INNER JOIN venues v2 ON v2.userID = f.friendID WHERE v1.venueID = v2.venueID v1.year = v2.year v1.month = v2.month v1.date = v2.date v1.hour = v2.hour 

You may need to use DISTINCT in a SELECT statement if there are more than two friends at the meeting place (or, if necessary, at the same time).