Help required writing a complex query - identifying pattern of a value and fetch results

Need help in writing an extremely complex query. Seems complex to me, may be trivial for experts.

Following are the two tables under consideration:

CREATE TABLE tbl_reco_index_contents (
id integer PRIMARY KEY,
reco_index_content_code char(10),
reco_index_content_name char(100),
package_name char(20),
downloaded smallint
)


CREATE TABLE tbl_reference_databases (
id integer PRIMARY KEY,
refdata_content_code char(25),
refdata_profession_passion_code char(10) )

Select allOpen in new window

My only target to get the results is reco_index_contents table. But, I need to get the result as follows:

reco_index_content_code  reco_index_content_name  package_name  passion_or_profession

You can notice that "passion_or_profession" field does not exist in both the tables above. In the second table refdata_profession_passion_code is a field which defines whether reco_index_content_code in table 1 is passion or profession . A typical passion code would be - 29-1069.AD and a typical profession code would be 02.05.02 . In short, a passion code will contain hyphens and dots whereas a profession code would only contain dots.

reco_index_content_code and refdata_content_code are the connection field between both the tables.


Is it possible to get the passion_or_profession(passion if refdata_profession_passion_code contain only dots, profession otherwise) as output of the query with the conditions mentioned above?

Any help is much appreciated.