1

This is probably a simple answer, but I've been working on this problem, for the past couple days, and it's starting to drive me batty

I have a database with a many to many relationship


-------------     ---------------------     ------------------- 
|Images     |     |user2img           |     |User             | 
-------------     ---------------------     ------------------- 
|imgID      |     |id                 |     |UserID           |
|imgURL     |     |imgID              |     |UserName         |
|imgAv      |     |UserID             |     |UserEmail        |
-------------     ---------------------     -------------------

The user2img table indicates which image the user is allowed to view.

I'm trying to determine a SQL query that will list every image (once without repeating) and tell me if the specified user has rights to see that image or not.

So an output like,

(WHERE UserID = '1')

----------------------------
|imgID   | imgURL | access |
----------------------------
|1       | xxx    |  Yes   |
|2       | yyy    |  No    |
|3       | qqq    |  Yes   |
|4       | rrr    |  Yes   |
|5       | www    |  No    |
|6       | sss    |  Yes   |
 etc...
----------------------------

Thanks,

C.d. Rudd
  • 11
  • 1

1 Answers1

0

One possibility would be (i admit not the best and not very efficient probably) to select all matching images for a user with inner join over the composite key table and UNION the result with the negated selection (iid is imageId in my test table):

select 
    iid, url, 'yes'
    from user_has_image
    inner join image on user_has_image.iid = image.id
    inner join user on user_has_image.uid = user.id
    where user.id = 1
union
select 
    iid, url, 'no'
    from user_has_image
    inner join image on user_has_image.iid = image.id
    inner join user on user_has_image.uid = user.id
    where user.id <> 1

You can see the result in this SQLFiddle session.

keenthinker
  • 7,645
  • 2
  • 35
  • 45
  • Would that also not give me an output that has all the users that don't have an ID of 1 (User 2, 3, 4, 5, 6, 7, 8... etc... as well as miss imgs that the user doesn't have listed in user2img table? – C.d. Rudd Oct 28 '13 at 23:44
  • Yes, but in your example from the question - would that not be the case for all users if there is no mapping in the user2image table? If the UserId is **NOT** present in the table, then the user has no access to that image. There is no other solution to the negation problem. You can rewrite the UNION to **left** or **right** join but it would be the same - every result that is null for UserId would be an image for all other users in the user2image table. I think you need to check only for access - if a *connection* using **inner join** is present. – keenthinker Oct 29 '13 at 18:54