mvpolyakov Posted January 15, 2009 Share Posted January 15, 2009 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! Quote Link to comment https://forums.phpfreaks.com/topic/141002-strange-mysql-behavior-help/ Share on other sites More sharing options...
Cosizzle Posted January 16, 2009 Share Posted January 16, 2009 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) Quote Link to comment https://forums.phpfreaks.com/topic/141002-strange-mysql-behavior-help/#findComment-738041 Share on other sites More sharing options...
mvpolyakov Posted January 16, 2009 Author Share Posted January 16, 2009 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??? Quote Link to comment https://forums.phpfreaks.com/topic/141002-strange-mysql-behavior-help/#findComment-738219 Share on other sites More sharing options...
fenway Posted January 18, 2009 Share Posted January 18, 2009 Huh? Quote Link to comment https://forums.phpfreaks.com/topic/141002-strange-mysql-behavior-help/#findComment-739725 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.