0

Select joined data from multiple tables in one query

Hi guys,

I'm pretty new to YQL but liking what I see so far.

I'm having a bit of trouble writing a query that I want to perform and am wondering if it's even possible.

I'm trying to retrieve some photos from Flickr by username and so far I've got it partially working with the following query:

CODE
SELECT 
*
FROM
flickr.photos.search
WHERE
user_id
IN (
SELECT
id
FROM
flickr.people.findbyusername
WHERE
username='username'
);


Now what I'd really like to do is a join of some sort on the flickr.photos.sizes table and have it return the available sizes for the photos returned by the query above.

The closest I've got is this:

CODE
SELECT 
source
FROM
flickr.photos.sizes
WHERE
photo_id
IN (
SELECT
id
FROM
flickr.photos.search
WHERE
user_id
IN (
SELECT
id
FROM
flickr.people.findbyusername
WHERE username='username'
)
);


Which returns a list of sizes for the images but doesn't group them by image and it doesn't return the titles or any other data.

Ideally, I'd like something like this returned:

CODE
[{
id: 1,
title: "Image 1 title",
sizes: {
s: "http://urltosmall",
m: "http://urltomedium"
}
},{
id: 2,
title: "Image 2 title",
sizes: {
s: "http://urltosmall",
m: "http://urltomedium"
}
}]


Is this possible with a single query?

Cheers,
Terry

by
1 Reply

Recent Posts

in YQL