Solved

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

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
The viewer will learn how to implement Singleton Design Pattern in Java.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now