title: Using SQL join and subquery to query two tables in R
body: <p>I'm a beginner.</p>
  
  <p>I have two .txt files and I'm using R with sqldf pakage to query them</p>
  
  <p>The first table (venues.txt) look like this:  </p>
  
  <pre><code>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
  </code></pre>
  
  <p>The second table (friends.txt) look like this:  </p>
  
  <pre><code>userID,friendID
  1,5
  1,9
  1,50
  1,102
  1,300
  </code></pre>
  
  <p>I want to query the venues (venueID) that a user (say userID=1) visited WITH one or more of his friends (friendID)</p>
  
  <p>Note: both userID,friendID of friends table could be linked to userID in venues table</p>
  
  <p>the query results should look like this:</p>
  
  <pre><code>venueID  friendID
  47       5
  47       9
  29       102
  86       102
  </code></pre>
  
  <p>I can do this using many separate queries and then join them in a table but my dataset is very larg. Is there any easier way to do this?</p>
  
  <p>I was able to query all venues that have been visited by a user or his friends:</p>
  
  <pre><code>sqldf("select userID, venueID from data
         where userID=1 OR userID IN (select friendID from freind where userID=1)")
  </code></pre>
  
  <p>Many thanks.</p>