Strange MySQL behavior - HELP!

[mvpolyakov]

Hi, I'm pretty new to SQL and I'm having a very strange issue. 

I have two separate but identical (I think) full installations - on my laptop and the server that are giving me different behavior.  Mainly the server is acting weird: 

 

To simplify, I have two tables in a mysql db, COMMUNITY and COMMUNITY_ADMIN.

The latter has this data:

COMMUNITY_ID  PERSON_ID

1                      1

25                      1

 

Using HeidiSQL as the interface, the query

"select community0_.COMMUNITY_ID as col_0_0_ from COMMUNITY community0_ "

returns 2 rows (all it has)

 

The query "select admins1_.PERSON_ID from COMMUNITY_ADMIN admins1_" returns two rows with 1 column, each cell containing a "1".  So far so good. 

 

However, when I combine them into --

 

"select community0_.COMMUNITY_ID as col_0_0_ from COMMUNITY community0_

where (1 in (select admins1_.PERSON_ID from COMMUNITY_ADMIN admins1_))"

 

(generated by hibernate, but that shouldn't matter) it returns 0 records where as it should return two since "(1 in (select admins1_.PERSON_ID from COMMUNITY_ADMIN admins1_))" should return true in all cases! 

 

The type of both fields is bigint, and i tried putting the 1 in single quotes, but nothing works on the server set up (though its fine on my laptop).  I don't understand how this can be happening...

 

Please let me know if you have any ideas or if the question is confusing!

[Cosizzle]

sorry, im not great with inner quries but try this:

 

SELECT
c.COMMUNITY_ID AS col_0_0_
FROM
COMMUNITY c INNER JOIN COMMUNITY_ADMIN ca ON (c.COMMUNITY_ID = ca.PERSON_ID)

[mvpolyakov]

Fortunately, hibernate lets me stay away from joins, but in any case, there is no reason at all to compare c.COMMUNITY_ID = ca.PERSON_ID.

 

I am really just wondering about the conditions where each component of my expression works, but putting them together does not.  I am afraid there is something i'm missing that will bite me elsewhere.  A workaround I'm sure I can find.  So any other property setting or syntax issue I'm not seeing?  Help???

[fenway]

Huh?