How would I solve this ?

Hi I have got a few relational tables. Simplefied it looks something
like this:


users (userId)
items (itemId,userId,itemName)
images (imageId)
images_items (imageId,itemId)

Users can upload Images and link that image to one or more items that
the user has defined

I can easlily select all items that belong to an image (imageId = 1)
SELECT * FROM items_images WHERE imageId='1';

How would I select all the items that belong to a specific image along
with the items that the user has defined but dont belong to that image
and distinguist between those 2. Btw I know the values of itemId and userId

Your help would be gratly appreciated because I dont know how to do this :-(


Floortje

On Apr 9, 4:16 pm, Floortje <n...@none.none> wrote:
> Hi I have got a few relational tables. Simplefied it looks something
> like this:
>
> users (userId)
> items (itemId,userId,itemName)
> images (imageId)
> images_items (imageId,itemId)
>
> Users can upload Images and link that image to one or more items that
> the user has defined
>
> I can easlily select all items that belong to an image (imageId = 1)
> SELECT * FROM items_images WHERE imageId='1';
>
> How would I select all the items that belong to a specific image along
> with the items that the user has defined but dont belong to that image
> and distinguist between those 2. Btw I know the values of itemId and userId

You mean you know the itemId?

If the desired userId is U, and the desired imageId is X,

SELECT * FROM items I LEFT JOIN images_items II
ON I.itemId=II.itemId AND II.imageId=X
WHERE II.itemId IS NOT NULL OR I.userId=U

I think that should be close. Drop me a line if you need more help.

>
> Your help would be gratly appreciated because I dont know how to do this :-(
>
> Floortje

On 9 Apr, 21:16, Floortje <n...@none.none> wrote:
> Hi I have got a few relational tables. Simplefied it looks something
> like this:
>
> users (userId)
> items (itemId,userId,itemName)
> images (imageId)
> images_items (imageId,itemId)
>
> Users can upload Images and link that image to one or more items that
> the user has defined
>
> I can easlily select all items that belong to an image (imageId = 1)
> SELECT * FROM items_images WHERE imageId='1';
>
> How would I select all the items that belong to a specific image along
> with the items that the user has defined but dont belong to that image
> and distinguist between those 2. Btw I know the values of itemId and userId
>
> Your help would be gratly appreciated because I dont know how to do this :-(
>
> Floortje

That query would not work. There is no table called items_images