Is it possible to get an intersection or a diff of two result sets in YQL?

Question

I need to build a Twitter module entirely on the client side.

I intended to use the Twitter API for the data and YQL to get across Same Origin Policy, and also for its higher rate limiting. I have used this combination before with success.

One thing I need to do is to get a list of tweets with a hashtag and from a finite set of users.

This gets tweets with the #stackoverflow hash tag by users alexdickson and lizardbill.

I also need to get a different set of tweets.

This gets all tweets with the #stackoverflow hash tag.

Now, I need to display the second collection but excluding the results from the first set. This would be a diff of the results, like PHP's array_diff().

For completeness, I may also need to achieve something by getting an intersection of two results, that is, a list of tweets which appear in both result sets. This is similar to PHP's array_intersect().

It has been a while since I've used SQL and can't think of how to achieve this in YQL.

alex · Answer 1 · 2012-01-20T04:01:00.287

2

Looks like I finally got it. YQL supports sub-queries.

SELECT results 
FROM   json 
WHERE  url = "http://search.twitter.com/search.json?q=%23stackoverflow" 
       AND results.id NOT IN (SELECT results.id 
                              FROM   json 
                              WHERE  url = 
"http://search.twitter.com/search.json?q=%23stackoverflow%20from%3aalexdickson,%20OR%20from%3alizardbill" 
)

YQL Console.

edited Jan 20 '12 at 04:01

answered Jan 20 '12 at 01:10

alex

479,566
201
878
984

You could also make use of the `twitter.search` community table in a similar manner. `select * from twitter.search where q="#stackoverflow" and id not in (select id from twitter.search where q="#stackoverflow from:alexdickson, OR from:lizardbill")` – salathe Jan 20 '12 at 11:41
@salathe Thanks for that. I never noticed the community tables. If you post that as an answer, I'll accept :) – alex Jan 23 '12 at 00:14

score 2 · Accepted Answer · answered Jan 23 '12 at 13:33

YQL supports sub-selects (subqueries). See Joining Tables with Sub-selects. Sub-selects are used with the IN operator, to filter the results of the outer select.

For your particular query, there is also a twitter.search Community Open Data Table available to save you from building the Twitter URLs and using the json table.

select *
from twitter.search
where q="#stackoverflow" 
and id not in (
    select id 
    from twitter.search 
    where q="#stackoverflow from:alexdickson, OR from:lizardbill"
)

^{(Try this query in the YQL console.)}

Is it possible to get an intersection or a diff of two result sets in YQL?

2 Answers2