need help with query to select with subquery

Question

need help with query to select with subquery

Denise McMillan

SSCertifiable

Points: 6216
More actions
November 29, 2011 at 4:40 pm

#251252

I'm having trouble getting this query to work and will appreciate any ideas.
select number, avalue, (select x.nvalue from valuestbl as x where a.number = x.number and x.number like '%n%') as nvalue, (select y.diffvalue from valuestbl as y where a.number = y.number and y.number like '%diff%')
from valuestbl as a where a.number like '%mean%' order by a.number
The data is like this:
number avalue
0500diff 4.2
0500mean 87.5
0500n 1
1006diff 3.2
1006mean 88.5
1006n 13
The results should be like:
number avalue nvalue diffvalue
0500 87.5 1 4.2
1006 88.5 13 3.2
But I only get nulls in the nvalue and diffvalue fields. What am I missing? Thanks very much for any help.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Denise McMillan (11/29/2011)
I'm having trouble getting this query to work and will appreciate any ideas.
select number, avalue, (select x.nvalue from valuestbl as x where a.number = x.number and x.number like '%n%') as nvalue, (select y.diffvalue from valuestbl as y where a.number = y.number and y.number like '%diff%')
from valuestbl as a where a.number like '%mean%' order by a.number
The data is like this:
number avalue
0500diff 4.2
0500mean 87.5
0500n 1
1006diff 3.2
1006mean 88.5
1006n 13
The results should be like:
number avalue nvalue diffvalue
0500 87.5 1 4.2
1006 88.5 13 3.2
But I only get nulls in the nvalue and diffvalue fields. What am I missing? Thanks very much for any help.

mean and n both are like %n% so that's going to foul you up a bit there.

Have you looked into the PIVOT Join mechanic? That's pretty much what you want here. Else poke around here for crosstab queries.

With schema/sampledata/usable query like you'll find in the first link in my signature, we could help you further with the query directly.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Revenant SSC-Forever Points: 42467 More actions · Answer 2

I think that even without looking at your DDL I can say that you cannot get the result you say you expect: you start by

SELECT number, ...

but then you test

where a.number like '%mean%'

If 'number' is the column containing values like 'mean', you will not get rows starting with your first column (which contains '0500' or '1006'.

Revenant SSC-Forever Points: 42467 More actions · Answer 3

Joe, that still falls short - please note my observation that you cannot test a column for '%diff%' yet expect it to show '0500'. 🙂

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

Have you tried normalising out the first column into two, say in a CTE? If the numeric component is always the first four characters, you could do this easily with left().

[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]

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 5

I got it to work by joining just on the left 4 charracters of the number in the subquery.

select number, avalue, (select x.nvalue from valuestbl as x where left(a.number, 4) = left(x.number, 4) and x.number like '%n%') as nvalue, (select y.diffvalue from valuestbls y where left(a.number, 4) = left(y.number, 4) and y.number like '%diff%') as diffvalue

from valuestbl as a where a.number like '%mean%' order by a.number

Thanks very much to everyone for the help.

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

Denise McMillan (11/30/2011)
I got it to work by joining just on the left 4 charracters of the number in the subquery.
select number, avalue, (select x.nvalue from valuestbl as x where left(a.number, 4) = left(x.number, 4) and x.number like '%n%') as nvalue, (select y.diffvalue from valuestbls y where left(a.number, 4) = left(y.number, 4) and y.number like '%diff%') as diffvalue
from valuestbl as a where a.number like '%mean%' order by a.number
Thanks very much to everyone for the help.

The code doesn't run Denise, there are several errors in it.

Take a peek at the following code which might point you in the right direction:

DROP TABLE #valuestbl

CREATE TABLE #valuestbl (number VARCHAR(10), avalue DECIMAL (6,2), nvalue DECIMAL (6,2), diffvalue DECIMAL (6,2))

INSERT INTO #valuestbl (number, avalue, nvalue, diffvalue)

SELECT '0500diff', 4.2, 42, 420 UNION ALL

SELECT '0500mean', 87.5, 875, 8750 UNION ALL

SELECT '0500n', 1, 10, 100 UNION ALL

SELECT '1006diff', 3.2, 32, 320 UNION ALL

SELECT '1006mean', 88.5, 885, 8850 UNION ALL

SELECT '1006n', 13, 130, 1300

;WITH Normaliser AS (

SELECT a.*, x.*

FROM #valuestbl a

CROSS APPLY(SELECT Leftbit = LEFT(a.number,4), Rightbit = SUBSTRING(a.number,5,LEN(a.number)-4)) x

)

SELECT *

FROM Normaliser

^{“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