subquery - Mysql query using 3 table returns wrong data -

table 1 -> access control

1) user_uuid 2) product_id 3) facebook_uuid 

table 2 -> product table

1) product_id 2) product_name 3) status 4) visibility 5) result 6) user_uuid 

table 3 -> bidding table

1) product_id 2) user_uuid 3) options 

sample data

product table :-

id - name - status - visibility - result  - user_uuid 1  - t1   - 1      -     1      -   0     -    1 2  - t2   - 1      -     1      -   0     -    1 3  - t3   - 0      -     0      -   1     -    1 

access control

user_uuid    -    product_id    -    facebook_uuid      1        -        1         -         123     1        -        1         -         456     1        -        1         -         789      1        -        2         -         123     1        -        2         -         456     1        -        2         -         789      1        -        3         -         123     1        -        3         -         456     1        -        3         -         789 

bidding table :- table won't have data once user bid on product data entered.

product_id   -   user_uuid   - options    1         -      2        -  123.35 

user table :-

user_uuid   -   facebook_uuid    1        -      007    2        -      123    3        -      456    4        -      789 

explanation :-

user 1 created 3 product , share other facebook users. managing sharing have create access control table track records of product shared other users user 1. can check in access control table.

now products shared others users able see products on screen shared them.

we have 2 tabs on screen -> shared & bid.

shared -> here users see products shared them have not bid on product yet. once user bid on product make entry in bid table shown above.

bid -> here users see products have bid.

our query :-

select *  product table  product_id in      ( select product_id        access table        facebook_uuid ='123'          , product_id not in             ( select product_id bid table)      )   or product_id in      ( select product_id        access table        user_uuid  in             ( select user_uuid               user table               facebook_uuid = '123'             )         , product_id not in             ( select product_id bid table )      )  , result = 0  , status = 1  , visibility = 1 

this should return me product 2 user 2 has placed bid product 1 , product 3 status & visibility criteria not matching. above query giving me product 2 & 3 both wrong.

we using 3 table getting data , looking of expert guide.

note - have check 2 things importantly unable in our query that, user must not have bid , shared user product creator.

first subquery selects products shared given user, , second subquery selects products given user bid on. query selects product in first list (selected first subquery) not in second list (selected second subquery).

select * product_table     result=0 ,     status=1 ,     visibility=1 ,     product_id in (         select product_id                     access_table         facebook_uuid='123'     ) ,     product_id not in (         select product_id                     bid_table join             user_table on user_table.user_uuid=bid_table.user_uuid         facebook_uuid='123'     ) 

http://www.sqlfiddle.com/#!2/d59cb/2