Обсуждение: 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