hadoob024 Posted February 20, 2008 Share Posted February 20, 2008 Here's my problem, but first, some relevant info. Here's my first table (SER_CONTEST): ID TYPE_OF_USER -------------------------------------------------------------------- 51 SER_JOB_TYPE_PROMOTER 69 SER_JOB_TYPE_KITCHEN_STAFF:SER_JOB_TYPE_PROMOTER 46 - Here's my second table (SER_ENITY_USER): ID INTERESTED_POSITIONS ------------------------------------------------------------------------ 141 SER_JOB_TYPE_BARBACK/BUSSER/RUNNER Here's my query: SELECT UNIQUE SER_CONTEST.ID FROM SER_CONTEST, SER_ENITY_USER WHERE DRAWING_POINT >= SYSDATE AND (SER_CONTEST.TYPE_OF_USER LIKE SER_ENITY_USER.INTERESTED_POSITIONS || '%' OR SER_ENITY_USER.INTERESTED_POSITIONS LIKE SER_CONTEST.TYPE_OF_USER || '%' ) This query is pulling back 2 records (contest ID's of 51 and 69) with the current data in the tables, even though it should pull back 0 records for this particular query. Only when I change my second table (SER_ENITY_USER) to this should it pull back 2 records: ID INTERESTED_POSITIONS ------------------------------------------------------------------------ 141 SER_JOB_TYPE_PROMOTER Is there a typo on my part? I'm not sure why it's not working. I tried various combinations of wildcards and even using the soundex() function, but none of them work (I'm guessing soundex() isn't working because each db entry starts with "SER_JOB_TYPE_". Any thoughts? Thanks in advance!!! Quote Link to comment Share on other sites More sharing options...
hadoob024 Posted February 20, 2008 Author Share Posted February 20, 2008 Sorry. I had a COMPLETE brain fart! Can you please move this topic to the Oracle forums as my question is related to the Oracle DBMS? Thanks! Quote Link to comment Share on other sites More sharing options...
toplay Posted February 20, 2008 Share Posted February 20, 2008 I've forgotten everything about Oracle now, but with the "LIKE" you usually use the "%" next to the value or column. Examples: column_name LIKE "%nuts" # Finds everything ending with nuts...i.e. donuts or lug nuts column_name LIKE "bicycle%" # Finds everything starting with bicycle...i.e. bicycle shop column_name LIKE CONCAT('%', other_column_name, '%') # Checks if column_name contains anywhere the value of other_column_name So I doubt the syntax you have of "|| % OR" will work. Check Oracle site/manual for proper syntax. Good luck. Quote Link to comment Share on other sites More sharing options...
hadoob024 Posted February 20, 2008 Author Share Posted February 20, 2008 I've been working on it all day, and here's where I am. I tried something similar to that, but here's the problem that I was having: Here's my second table (SER_ENITY_USER) now: ID INTERESTED_POSITIONS ------------------------------------------------------------------------ 141 SER_JOB_TYPE_PROMOTER:SER_JOB_TYPE_BOUNCER/SECURITY Now if I run my updated query: SELECT UNIQUE SER_CONTEST.ID FROM SER_CONTEST, SER_ENITY_USER WHERE DRAWING_POINT >= SYSDATE AND SER_ENITY_USER.ENITY_ID = '141' AND ('%' || SER_CONTEST.TYPE_OF_USER || '%' LIKE '%' || SER_ENITY_USER.INTERESTED_POSITIONS || '%' OR '%' || SER_ENITY_USER.INTERESTED_POSITIONS || '%' LIKE '%' || SER_CONTEST.TYPE_OF_USER || '%') With this query, it only pulls back the record with ID of 51, but it won't pull back the record with ID of 69. I also noticed that if I change my table (SER_CONTEST) to the following: ID TYPE_OF_USER ----------------------------------------------- 51 SER_JOB_TYPE_PROMOTER 69 SER_JOB_TYPE_KITCHEN_STAFF:SER_JOB_TYPE_PROMOTER:SER_JOB_TYPE_OTHER 46 - Then my query pulls back the proper 2 records (with ID's of 51 and 69). Did I mess up on my query, or is this a different issue that hasn't been taken into account? Basically I want to find if any of the items for the value of a field appears in any items of the items for the value of the other field. Like where if TYPE_OF_USER equals: SER_JOB_TYPE_PROMOTER:SER_JOB_TYPE_BARBACK/BUSSER/RUNNER And where if INTERESTED_POSITIONS equals: SER_JOB_TYPE_KITCHEN_STAFF:SER_JOB_TYPE_PROMOTER:SER_JOB_TYPE_OTHER This produces a match. Or like where if TYPE_OF_USER equals: SER_JOB_TYPE_BARBACK/BUSSER/RUNNER:SER_JOB_TYPE_PROMOTER:SER_JOB_TYPE_BARTENDER And where if INTERESTED_POSITIONS equals: SER_JOB_TYPE_KITCHEN_STAFF:SER_JOB_TYPE_BARTENDER:SER_JOB_TYPE_OTHER This should also produce a match. I tried using soundex(), but the whole "SER_JOB_TYPE_" part keeps producing a match. Is there anyway to disregard that part when doing soundex()? Quote Link to comment Share on other sites More sharing options...
gizmola Posted March 2, 2008 Share Posted March 2, 2008 Not one but two tables that should be many to many resolvers, and you wonder why you're struggling? I hope you realize that even if you get these queries working, Oracle can not use indexes at all with the approach that has been taken in storing this data. With that said, the INSTR or REGEXP_LIKE functions might be of interest to you. Quote Link to comment 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.