Thread: Easiest way to extract owner-id from a third table
I have four tables: users (id, name, ...); components (id, owner_id (references users), ...); images (id, filename); component_images (id, components_id (references components), images_id (references images)); This way, I can have unlimited amount of images per component. Now I'm looking for a way to extract the owner-id (that's specified in the components table) and the owner name while having only the image_id at hand. I can ofcourse first select a row in component_images and - in the application - extract the value for components_id. Then I can use another query to find the owner id in the components table, which will let me have the owner id. Is there a way to achive all this without having to fiddle around in the application? I haven't got the faintest idea of how to specify this query. -- - Rikard
On 19 August 2010 18:40, Rikard Bosnjakovic <rikard.bosnjakovic@gmail.com> wrote: > I have four tables: > > users (id, name, ...); > components (id, owner_id (references users), ...); > images (id, filename); > component_images (id, components_id (references components), images_id > (references images)); > > This way, I can have unlimited amount of images per component. > > Now I'm looking for a way to extract the owner-id (that's specified in > the components table) and the owner name while having only the > image_id at hand. I can ofcourse first select a row in > component_images and - in the application - extract the value for > components_id. Then I can use another query to find the owner id in > the components table, which will let me have the owner id. > > Is there a way to achive all this without having to fiddle around in > the application? I haven't got the faintest idea of how to specify > this query. > Try this: SELECT components.owner_id FROM components INNER JOIN component_images ON components.id = component_images.components_id WHERE component_images.images_id = % LIMIT 1 -- Thom Brown Registered Linux user: #516935
On Thu, Aug 19, 2010 at 19:46, Thom Brown <thom@linux.com> wrote: > Try this: > > SELECT components.owner_id > FROM components > INNER JOIN component_images ON components.id = component_images.components_id > WHERE component_images.images_id = % > LIMIT 1 Magic! Thank you. -- - Rikard