Subquery problem

Question

Post reply

Subquery problem

vinay.naran

Grasshopper

Points: 20
More actions
December 1, 2014 at 1:53 pm

#292410

I have a one table like
PartCat
1101
2102
3103
4104
5105
6106
7107
8108
9109
10110
and other table like
PartCatQty
110140
4NULL20
NULL10510
NULL10830
1011050
7NULL70
I want below result using subquery or any other method
PartCatQty
110140
21020
31030
410420
510510
61060
710770
810830
91090
1011050
Please help me.
Regards
Vinay

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

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

g.britton SSChampion Points: 13863 More actions · Answer 1

-- I have a one table like

declare @t1 table (Part int,Cat int)

insert into @t1(Part, cat) values

(1,101),

(2,102),

(3,103),

(4,104),

(5,105),

(6,106),

(7,107),

(8,108),

(9,109),

(10,110)

-- and other table like

declare @t2 table (Part int, Cat int,Qty int)

insert into @t2(Part, Cat, Qty) values

(1,101,40 ),

(4,NULL ,20),

(NULL,105,10),

(NULL,108,30),

(10,110,50 ),

(7,NULL ,70)

select t1.part, t1.cat, isnull(t2.qty ,0) qty

from @t1 t1

left join @t2 t2

on t1.part = t2.part

Gerald Britton, Pluralsight courses

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 2

My question would be, see what happens when you have denormalized data in your tables? Table 2 should NOT have the Cat column in it.

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

vinay.naran Grasshopper Points: 20 More actions · Answer 3

Hi,

it's working i have just added

t1.cat= t2.cat

select t1.part, t1.cat, isnull(t2.qty ,0) qty

from @t1 t1

left join @t2 t2

on t1.part = t2.part

or t1.cat= t2.cat

Thank you very much

Vinay