0

in my Application (Image Voting) I have several Roles for User, Voter & Admin.

The Select for the User looks like this:

SELECT * from wp_awa_upload, category, subcategory 
WHERE wp_awa_upload.uid = '$_SESSION[id]' 
AND wp_awa_upload.parent_cat = category.cat_id 
AND wp_awa_upload.sub_cat = subcategory.id

A user can just see his own Images, but a Voter should see all the images on which he had not voted. When a Voter is voting, there will be a new entry into table "wp_awa_session" with the ID of the Uploaded Image and his own ID. So the Select above has to be adopted with a "and where the Session_ID and the Uploaded ID are not in table wp_awa_session" condition.

How can I achieve this?

Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
Bosstone
  • 189
  • 1
  • 12
  • Probably better ways to do this with OUTER JOINS or INNER JOINS but does this work? SELECT wau.*, c.*, s.* from wp_awa_upload wau JOIN category c ON c.cat_id = wau.parent_cat JOIN subcategory s ON s.id = wau.sub_cat WHERE wau.uid = '$_SESSION[id]' AND '$_SESSION[id]' NOT IN (SELECT uid FROM wp_awa_upload WHERE uid = '$_SESSION[id]) – Shad Mickelberry Apr 24 '15 at 21:07
  • Do not use `,` shorthand for join. Try to use `ON()` or `USING()` instead of `WHERE` clauses for joining. It makes the query way more readable. [Teoman's answer here](http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins) might help you. – Fox Apr 24 '15 at 22:18

1 Answers1

0

this is the correct Select :-)

SELECT * 
FROM wp_awa_upload wau
JOIN category c ON ( wau.parent_cat = c.cat_id ) 
JOIN subcategory s ON s.id = wau.sub_cat
WHERE STATUS =  '1'
AND (
wau.id, wau.uid
) NOT 
IN (

SELECT upload_id, user_id
FROM wp_awa_session
WHERE user_id =  '$_SESSION[id]'
AND upload_id = wau.id
)

Thanks for your help :-)

Bosstone
  • 189
  • 1
  • 12