select foreach row in the select----How? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select foreach row in the select----How?

Author

Topic

seronni
Starting Member

1 Post

Posted - 2008-07-06 : 11:42:06

Hi ALL!

I have 3 tables and i get the values and union all with another select,
but i want search foreach row in this select if has more items in the table 2.
How i Do that with an performance way?
Example my T-SQL look like this:

TB1
ITEM1_COD NAME UNIM1_COD
31790 NAME1 1
11 NAME2 2
337 NAME3 3
342 NAME4 4
123 NAME5 5
98 NAME6 6
22 NAME7 7
1233 NAME8 8
19 NAME9 9
20 NAME10 10
1568 NAME11 11
3777 NAME12 12
3222 NAME13 13

TB2
ITEM1_COD ITEM1_COD0 QTD
11 31790 1,2
337 31790 1,9
342 31790 0,7
123 31790 1,2
98 31790 12
22 31790 22
1233 31790 2,4
19 22 2,5
20 22 22,2
1568 22 1,9
3777 22 3,5
3222 22 2,5
3146 23 3,7
13663 19 3,5
15345 19 3,5
334334 23 3,9
4415 25 3,5

TB3
UNIM1_COD NAME
1 a
2 b
3 c
4 d
5 e
6 f
7 g
8 h
9 i
10 j
11 k
12 l
13 m

ALTER PROCEDURE [dbo].[MySelect]
(
@COD int
)
AS
SET NOCOUNT ON;
//Get the Name in the TB1, QTD in the TB2 and Name in TB3 where the atribute Item1_COD0 in TB2 = an specific number
SELECT TB1.NAME,TB2.QTD,TB3.NAME
FROM TB1 INNER JOIN
TB2 ON TB1.ITEM1_COD = TB2.ITEM1_COD INNER JOIN
TB3 ON TB1.UNIM1_COD = TB3.UNIM1_COD
WHERE (TB2.ITEM1_COD0 = @COD)

UNION ALL

//get the name in TB1 and the Name in TB3 and Union whith the select before where the atribute ITEM1_COD in TB1 = the specific number
SELECT TB1.NAME,NULL,TB3.NAME
FROM TB1 AS TB1_1 INNER JOIN
TB3 AS TB3_1 ON TB1_1.UNIM1_COD = TB3_1.UNIM1_COD
WHERE (TB1_1.ITEM1_COD = @COD)

this code when the @COD = 31790 for example may give me this:

NAME QTD NAME

NAME2 1,2 b
NAME3 1,9 c
NAME4 0,7 d
NAME5 1,2 e
NAME6 12 f
NAME7 22 g
NAME8 2,4 h
NAME1 NULL a

but if u pay attention in TB2 in the field 'ITEM1_COD' the element 22 and 19 are an subelement of the 31790. The
element 22 have more 5 element and the element 19 have more 2 elements and i need to get them too...how i do that?

Can anyone, please help me!?

TKS,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-07 : 00:17:47

Can you show what your expected output will be from sample data above?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources