mysql right join with not in (select)

Question

I'm having problems with a reasonably complex (to me) query. Each of the individual pieces work, it produces no errors but it doesn't return what it should be returning.

table_c = things to belong to
table_s = users that belong to things in table_c
table_u = users

And the query is:

SELECT * FROM table_c AS C 
RIGHT JOIN table_u AS U ON C.userid = U.user_id 
WHERE C.userid='xxx' 
  AND C.c_county IN (11, 00) 
  AND C.c_state IN (12, 00) 
  AND C.c_nation='US' 
  AND C.c_privacy='0' 
  AND C.userid NOT IN ( 
    SELECT userid FROM table_s AS S WHERE S.channel_id = C.id)
ORDER BY U.security, C.chan_name

What I need to do is select all the "things to belong to" in table_c where table_c's county/state/nation match the users county/state/nation settings in table_u or "things to belong to" is 00 (which has no location) and where the userid is not already in table_s.

I create a playground in table_c located in county #11 in state #12 and users from county #11 and state #12 want to find all the things they can belong to so they query should return my playground.

This only works when the userid in WHERE C.userid='xxx' belongs to whomever created the playground.
What I want is for users to create something to belong to and then let other people join that something so WHERE userid='xxx' part isn't right and I'm not sure how to write it...

Is this clear or clear as mud?

got the table schema definitions? – dispake Jun 07 '12 at 17:18 — dispake, Jun 07 '12 at 17:18
http://sqlfiddle.com/ use it will be easier to us – jcho360 Jun 07 '12 at 17:36 — jcho360, Jun 07 '12 at 17:36

score 0 · Accepted Answer · answered Jun 07 '12 at 18:02

0

the answer is to remove the join completely... since I already have the user info needed to build the county/state/nation I also have everything else so no need to do the join...

Thanks for the extra eyes!

answered Jun 07 '12 at 18:02

ppetree

826
3
15
31

mysql right join with not in (select)

1 Answers1