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
Solved

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

Posted on 2013-06-06
3
377 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
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 48

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

809 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