TSQL Complex Search

Question

Post reply

TSQL Complex Search

zainali2006

SSC Rookie

Points: 43
More actions
April 26, 2011 at 4:52 am

#236375

Main data file
Data File
CodeIGPDescriptionGroupOrItem
ABCProfileXYZBCC
ABCProfileYZXCCC
ABCProfileCXYDDD
ABCItemYXCIA1
LL1ProfileTestKLM
LL1ProfileTestBCC
LL1ProfileTestCCQ
LL1ProfileTestDDD
LL1ItemTestIA1
LL2ProfileTestMLK
LL2ProfileTestIA1
LL2ProfileTestBCC
LL2ProfileTestCCQ
IA1ItemDiesel.
BCCGroupTestCCQ
BCCItemTestIA1
Hi,
I am trying to display records based on user search example if user looks for code:ABC The list of records it should fetch as follows:
Search Results
CodeCodeDescriptionGroupOrItem
ABCProfileXYZBCC
ABCProfileYZXCCC
ABCProfileCXYDDD
ABCItemYXCIA1
LL1ProfileTestKLM
LL1ProfileTestBCC
LL1ProfileTestCCC
LL1ProfileTestDDD
LL1ItemTestIA1
IA1ItemDiesel.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004706 More actions · Answer 1

I believe you're going to have to explain that one. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

zainali2006 SSC Rookie Points: 43 More actions · Answer 2

Here is the complete script and output

DECLARE @Sample TABLE

(

Code CHAR(4) ,

IGP VARCHAR(255) ,

[Description] VARCHAR(255) ,

GroupOrItem CHAR(4)

) ;

INSERT INTO @Sample

VALUES ( 'ABC', 'Profile', 'XYZ' , 'BCC' ),

( 'ABC', 'Profile', 'XYZ' , 'CCC' ),

( 'ABC', 'Profile', 'XYZ' , 'DDD' ),

( 'ABC', 'Group' , 'XYZ' , 'IA1' ),

( 'LL1', 'Profile', 'Test', 'BCC' ),

( 'LL1', 'Profile', 'Test', 'CCC' ),

( 'LL1', 'Profile', 'Test', 'DDD' ),

( 'LL1', 'Group' , 'Test', 'IA1' ),

( 'LL2', 'Profile', 'Test', 'MLK' ),

( 'LL2', 'Group' , 'Test', 'IA1' ),

( 'LL2', 'Profile', 'Test', 'BCC' ),

( 'LL2', 'Profile', 'Test', 'CCQ' ),

( 'LL3', 'Profile', 'Test', 'BCC' ),

( 'LL3', 'Profile', 'Test', 'CCC' ),

( 'LL3', 'Profile', 'Test', 'DDD' ),

( 'LL4', 'Profile', 'Test', 'BCC' ),

( 'LL4', 'Profile', 'Test', 'CCC' ),

( 'LL4', 'Profile', 'Test', 'DDD' ),

( 'LL4', 'Group' , 'Test', 'IA1' ),

( 'IA1', 'Group' , 'YXC' , 'AAA1'),

( 'IA1', 'Group' , 'YXC' , 'AAA2'),

( 'IA1', 'Group' , 'YXC' , 'AAA3'),

( 'IA1', 'Group' , 'YXC' , 'AAA4'),

( 'BCC', 'Group' , 'Test', 'CCQ' ),

( 'AAA1', 'Item' , 'CTT' , '.'),

( 'AAA2', 'Item' , 'TCT' , '.'),

( 'AAA3', 'Item' , 'TTC' , '.'),

( 'AAA4', 'Item' , 'RCT' , '.')

;

SELECT *

FROM @Sample

WHERE GroupOrItem IN ( SELECT GroupOrItem

FROM @Sample

WHERE Code = 'ABC' ) The output is:

CodeIGPDescriptionGroupOrItem

ABC ProfileXYZBCC

ABC ProfileXYZCCC

ABC ProfileXYZDDD

ABC GroupXYZIA1

LL1 ProfileTestBCC

LL1 ProfileTestCCC

LL1 ProfileTestDDD

LL1 GroupTestIA1

LL2 GroupTestIA1

LL2 ProfileTestBCC

LL3 ProfileTestBCC

LL3 ProfileTestCCC

LL3 ProfileTestDDD

LL4 ProfileTestBCC

LL4 ProfileTestCCC

LL4 ProfileTestDDD

LL4 GroupTestIA1

Infact this should be: