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) )

Open 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.
Sreejith22Asked:
Who is Participating?
 
Simone BSenior E-Commerce AnalystCommented:
If I understand your question correctly, this may give you what you need.

SELECT a.reco_index_content_code, a.reco_index_content_name, a.package_name,
CASE WHEN b.refdata_profession_passion_code LIKE '%-%' THEN 'Passion' ELSE 'Profession' END AS passion_or_profession
FROM tbl_reco_index_contents a
INNER JOIN tbl_reference_databases b ON a.reco_index_content_code = b.refdata_content_code

Open in new window


However, I'm not sure that I got the right one for those containing hyphens. From your post:

In short, a passion code will contain hyphens and dots whereas a profession code would only contain dots.
... also ...
(passion if refdata_profession_passion_code contain only dots, profession otherwise)
0
 
ThomasianCommented:
SELECT
	C.reco_index_content_code
	,C.reco_index_content_name
	,C.package_name
	,[passion_or_profession] =
		CASE WHEN ISNULL(CHARINDEX('.',D.refdata_profession_passion_code),0)=0 THEN ''
	         WHEN CHARINDEX('-',D.refdata_profession_passion_code)>0 THEN 'passion'
	         ELSE 'profession'
	    END
FROM
	tbl_reco_index_contents C INNER JOIN
	tbl_reference_databases D ON C.reco_index_content_code = D.refdata_content_code

Open in new window

0
 
PortletPaulfreelancerCommented:
>>reco_index_content_code and refdata_content_code are the connection field(s)
one is char(10), the other is char(25) this may be a problem if values in one table are allowed that the other cannot support.

Regrettably you have both MySQL and MS SQL in both tags and topics, hence it is almost impossible to tell which dbms you are actually using (please don't do this, choose one, or tell us it is for both). The tables provided can be used in both dbms types also - so that does not narrow it down either.

So, on the off-chance the question relates to MySQL, try this:
SELECT
        C.reco_index_content_code
      , C.reco_index_content_name
      , C.package_name
      , CASE 
            WHEN LOCATE('-', D.refdata_profession_passion_code) > 0 THEN 'passion'
            WHEN LOCATE('.', D.refdata_profession_passion_code) > 0 THEN 'profession'
            ELSE NULL
        END as prof_or_passion
FROM tbl_reco_index_contents C
INNER JOIN tbl_reference_databases D ON C.reco_index_content_code = D.refdata_content_code

Open in new window

see: http://sqlfiddle.com/#!9/bf043/1 (MySQL)
for MS SQL change 'LOCATE' to 'CHARINDEX'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.