Query multiple descriptions from single field of codes

Question

Post reply

Query multiple descriptions from single field of codes

gadall

Grasshopper

Points: 12
More actions
January 23, 2010 at 8:36 pm

#218512

I have two Tables, A -- has a bunch of data I retrieve with the relevant (to this problem) field being FCode -- char(10)
FCode can have nothing ('') or 1-3 failure codes made up of Fxx where x = number.
There is a related table B that has
FCode - char(3)
FCodeDescription - char(50)
What I need is when I query Table A I need to have the FCodeDescription (if applicable) returned inline withe the other data, the issue I'm hving is how to get the description back when there is more than one FCode. i.e. FCode in Table A = 'F02F05'.
Any help is appreciated.

Viewing 10 posts - 1 through 9 (of 9 total)

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

G.R.Prithiviraj Kulasingham SSCarpal Tunnel Points: 4105 More actions · Answer 1

You need to write a function which turns the multi valued char(10) column into multi rowed table.

Then you can join that table to TableB and get the values needed.

I assume that you always send one column from TableA

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/

LutzM SSC Guru Points: 107049 More actions · Answer 2

Since the multi valued column is char(10), there can be a maximum of three values (btw: I'd rather use a char(9) length...).

Instead of calling the function I'd probably do it inside the query.

However, having a split string function available will help a lot if there are more columns holding multiple values (e.g. comma separated lists).

DECLARE @t TABLE (id INT, code CHAR(10))

INSERT INTO @t

SELECT 1,'F02F05F03' UNION ALL

SELECT 2,'F05F03' UNION ALL

SELECT 3,'F02F05' UNION ALL

SELECT 4,'F02'

SELECT

id,

SUBSTRING(code,3*n+1, 3) AS code

FROM @t

CROSS APPLY

(

SELECT 0 AS n UNION ALL

SELECT 1 UNION ALL

SELECT 2

) b

WHERE n < LEN(code)/3

/* result set:

idcode

1F02

1F05

1F03

2F05

2F03

3F02

3F05

4F02

*/

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

Here's a completely different way which returns the codes in the same row.

CREATE TABLE #A (id INT, FCode CHAR(10))

INSERT INTO #A

SELECT 1,'F02F05F03' UNION ALL

SELECT 2,'F05F03' UNION ALL

SELECT 3,'F02F05' UNION ALL

SELECT 4,'F02'

CREATE TABLE #B (FCode CHAR(3), FCodeDescription VARCHAR(20))

INSERT INTO #B (FCode, FCodeDescription)

SELECT 'F02', 'F02 description' UNION ALL

SELECT 'F03', 'F03 description' UNION ALL

SELECT 'F04', 'F04 description' UNION ALL

SELECT 'F05', 'F05 description' UNION ALL

SELECT 'F06', 'F06 description'

SELECT a.*,

'#' AS '#',

b1.FCodeDescription AS Desc1,

b2.FCodeDescription AS Desc2,

b3.FCodeDescription AS Desc3

FROM #A a

LEFT JOIN #B b1 ON b1.FCode = SUBSTRING(a.FCode, 1, 3)

LEFT JOIN #B b2 ON b2.FCode = SUBSTRING(a.FCode, 4, 3)

LEFT JOIN #B b3 ON b3.FCode = SUBSTRING(a.FCode, 7, 3)

Results:

id FCode # Desc1 Desc2 Desc3

----------- ---------- ---- -------------------- -------------------- --------------------

1 F02F05F03 # F02 description F05 description F03 description

2 F05F03 # F05 description F03 description NULL

3 F02F05 # F02 description F05 description NULL

4 F02 # F02 description NULL NULL

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 4

There's yet another way to do this if you want an output row for each code - with OR in the join to the lookup. This isn't often recommended because the performance can be poor, so adding in this join as a last step after any preaggregation of the main query would be the way to go, something like this:

SELECT a.*, b1.FCodeDescription

FROM (SELECT id, FCode FROM #A WHERE 1 = 1) a

LEFT JOIN #B b1

ON b1.FCode = SUBSTRING(a.FCode, 1, 3)

OR b1.FCode = SUBSTRING(a.FCode, 4, 3)

OR b1.FCode = SUBSTRING(a.FCode, 7, 3)

ORDER BY a.id

Cheers

ChrisM@home

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

gadall Grasshopper Points: 12 More actions · Answer 5

This works perfectly for what I have to do.

Thank you so much.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

lobbymuncher (1/24/2010)
Here's a completely different way which returns the codes in the same row.
CREATE TABLE #A (id INT, FCode CHAR(10))
INSERT INTO #A
SELECT 1,'F02F05F03' UNION ALL
SELECT 2,'F05F03' UNION ALL
SELECT 3,'F02F05' UNION ALL
SELECT 4,'F02'
CREATE TABLE #B (FCode CHAR(3), FCodeDescription VARCHAR(20))
INSERT INTO #B (FCode, FCodeDescription)
SELECT 'F02', 'F02 description' UNION ALL
SELECT 'F03', 'F03 description' UNION ALL
SELECT 'F04', 'F04 description' UNION ALL
SELECT 'F05', 'F05 description' UNION ALL
SELECT 'F06', 'F06 description'
SELECT a.*,
'#' AS '#',
b1.FCodeDescription AS Desc1,
b2.FCodeDescription AS Desc2,
b3.FCodeDescription AS Desc3
FROM #A a
LEFT JOIN #B b1 ON b1.FCode = SUBSTRING(a.FCode, 1, 3)
LEFT JOIN #B b2 ON b2.FCode = SUBSTRING(a.FCode, 4, 3)
LEFT JOIN #B b3 ON b3.FCode = SUBSTRING(a.FCode, 7, 3)
Results:
id FCode # Desc1 Desc2 Desc3
----------- ---------- ---- -------------------- -------------------- --------------------
1 F02F05F03 # F02 description F05 description F03 description
2 F05F03 # F05 description F03 description NULL
3 F02F05 # F02 description F05 description NULL
4 F02 # F02 description NULL NULL

Oh... be careful... you have to consider how hard the I/O system needs to work when doing multiple self joins. It's a very common performance problem.

--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)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

gadall (1/24/2010)
This works perfectly for what I have to do.
Thank you so much.

Heh... 3 solutions were given. Which one did you use?

--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)

gadall Grasshopper Points: 12 More actions · Answer 8

gadall

Grasshopper

Points: 12

January 24, 2010 at 10:48 pm

#1108005

The one with the 3 self joins.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Jeff Moden (1/24/2010)
lobbymuncher (1/24/2010)
Here's a completely different way which returns the codes in the same row.
Oh... be careful... you have to consider how hard the I/O system needs to work when doing multiple self joins. It's a very common performance problem.

Not near as bad as those non-sargable SUBSTRINGs 😉

Wonder if SUBSTRING(column, 1 ... is actually SARGable? Will attempt a test if time permits.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden