Solved

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

Posted on 2013-06-06
3
380 Views
Last Modified: 2013-07-02
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.
0
Comment
Question by:Sreejith22
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 11

Accepted Solution

by:
Simone B earned 350 total points
ID: 39226258
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
 
LVL 22

Assisted Solution

by:Thomasian
Thomasian earned 75 total points
ID: 39226277
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
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 75 total points
ID: 39228239
>>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

Featured Post

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question