Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-06-06
3
Medium Priority
?
382 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 1400 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 300 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 300 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
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.

783 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