MS SQL Server New Users - Querie & subquerie

Author

Querie & subquerie

Luiz Horacio

2005-03-30, 7:04 pm

Hi,

I'm trying to create a subquerie but it is returning a weird result.

Queries:

Querie 1: select grupos_tipos from tipo where num_abs = 8

Result 1: 33010013,33010021,33
010030,33010048,3301
0056,33010064

Querie 2: select mne from laudos_amb
where codigo in
(33010013,33010021,3
3010030,33010048,330
10056,33010064)

Result 2: all 6 mne are shown

Now the problem:

Querie 3: select mne from laudos_amb
where codigo in
(select grupos_tipos from tipo where num_abs = 8)

Result 3: Only the first mne is shown

What am I doing wrong? How can I make the subquerie work?

Regards,

Luiz Horacio

Rohtash Kapoor

2005-03-30, 7:04 pm

Are you using this statement somewhere in your script

SET ROWCOUNT 1

"Luiz Horacio" <lhoracio@iname.com> wrote in message
news:uSpO9$VNFHA.1884@TK2MSFTNGP15.phx.gbl...
> Hi,
>
> I'm trying to create a subquerie but it is returning a weird result.
>
> Queries:
>
> Querie 1: select grupos_tipos from tipo where num_abs = 8
>
> Result 1: 33010013,33010021,33
010030,33010048,3301
0056,33010064
>
>
> Querie 2: select mne from laudos_amb
> where codigo in
> (33010013,33010021,3
3010030,33010048,330
10056,33010064)
>
> Result 2: all 6 mne are shown
>
>
> Now the problem:
>
> Querie 3: select mne from laudos_amb
> where codigo in
> (select grupos_tipos from tipo where num_abs = 8)
>
> Result 3: Only the first mne is shown
>
>
> What am I doing wrong? How can I make the subquerie work?
>
>
> Regards,
>
>
> Luiz Horacio
>
>

Luiz Horacio

2005-03-30, 7:04 pm

Hi Rohtash Kapoor,

No.

Regards,

--
Luiz Horacio
lhoracio@ imadi. com. br

"Rohtash Kapoor" < rohtash_nospam@sqlma
ntra.com> escreveu na mensagem
news:#My$LaWNFHA.2252@TK2MSFTNGP15.phx.gbl...
> Are you using this statement somewhere in your script
>
> SET ROWCOUNT 1
>
>
>
>
> "Luiz Horacio" <lhoracio@iname.com> wrote in message
> news:uSpO9$VNFHA.1884@TK2MSFTNGP15.phx.gbl...
>
>

Rohtash Kapoor

2005-03-30, 7:04 pm

There doesn't seem to be anything wrong in the query:

Try this, and see if this is working:

USE Northwind

CREATE TABLE tipo
(
grupos_tipos INT,
num_abs INT
)

INSERT INTO tipo values (33010013,8)
INSERT INTO tipo values (33010021,8)
INSERT INTO tipo values (33010030,8)
INSERT INTO tipo values (33010048,8)
INSERT INTO tipo values (33010056,8)
INSERT INTO tipo values (33010064,8)
INSERT INTO tipo values (3336,9)
INSERT INTO tipo values (334,9)

CREATE TABLE laudos_amb
(
mne varchar(10),
codigo INT
)

INSERT INTO laudos_amb values ('a',33010013)
INSERT INTO laudos_amb values ('b',33010021)
INSERT INTO laudos_amb values ('c',33010030)
INSERT INTO laudos_amb values ('d',33010048)
INSERT INTO laudos_amb values ('e',33010056)
INSERT INTO laudos_amb values ('f',33010064)
INSERT INTO laudos_amb values ('z1',3)
INSERT INTO laudos_amb values ('z2',4)
INSERT INTO laudos_amb values ('z3',5)

SELECT grupos_tipos FROM tipo WHERE num_abs = 8

SELECT * FROM laudos_amb
WHERE codigo IN (33010013,33010021,3
3010030,33010048,330
10056,33010064)

SELECT mne FROM laudos_amb
WHERE codigo IN
(SELECT grupos_tipos FROM tipo WHERE num_abs = 8)

---
Rohtash Kapoor
http://www.sqlmantra.com

"Luiz Horacio" <lhoracio@iname.com> wrote in message
news:uSK0$bWNFHA.3144@tk2msftngp13.phx.gbl...
> Hi Rohtash Kapoor,
>
> No.
>
> Regards,
>
>
>
> --
> Luiz Horacio
> lhoracio@ imadi. com. br
>
>
> "Rohtash Kapoor" < rohtash_nospam@sqlma
ntra.com> escreveu na mensagem
> news:#My$LaWNFHA.2252@TK2MSFTNGP15.phx.gbl...
>
>

Luiz Horacio

2005-03-30, 7:35 pm

Hi Rohtash Kapoor,

No, just the same results... But I found a workaround:

Dim var_temp_subquerie as String
....
....

rst_test.open "select grupos_tipos from tipo where num_abs = 8"

var_temp_subquerie = rst_temp(0)

select mne from laudos_amb
where codigo in
(var_temp_subquerie)

Thanks for your help and interest.

Regards

Luiz Horacio

"Rohtash Kapoor" < rohtash_nospam@sqlma
ntra.com> escreveu na mensagem
news:ePJSBjWNFHA.4052@TK2MSFTNGP12.phx.gbl...
> There doesn't seem to be anything wrong in the query:
>
> Try this, and see if this is working:
>
> USE Northwind
>
> CREATE TABLE tipo
> (
> grupos_tipos INT,
> num_abs INT
> )
>
> INSERT INTO tipo values (33010013,8)
> INSERT INTO tipo values (33010021,8)
> INSERT INTO tipo values (33010030,8)
> INSERT INTO tipo values (33010048,8)
> INSERT INTO tipo values (33010056,8)
> INSERT INTO tipo values (33010064,8)
> INSERT INTO tipo values (3336,9)
> INSERT INTO tipo values (334,9)
>
>
> CREATE TABLE laudos_amb
> (
> mne varchar(10),
> codigo INT
> )
>
> INSERT INTO laudos_amb values ('a',33010013)
> INSERT INTO laudos_amb values ('b',33010021)
> INSERT INTO laudos_amb values ('c',33010030)
> INSERT INTO laudos_amb values ('d',33010048)
> INSERT INTO laudos_amb values ('e',33010056)
> INSERT INTO laudos_amb values ('f',33010064)
> INSERT INTO laudos_amb values ('z1',3)
> INSERT INTO laudos_amb values ('z2',4)
> INSERT INTO laudos_amb values ('z3',5)
>
>
> SELECT grupos_tipos FROM tipo WHERE num_abs = 8
>
> SELECT * FROM laudos_amb
> WHERE codigo IN
(33010013,33010021,3
3010030,33010048,330
10056,33010064)[colo
r=darkred]
>
> SELECT mne FROM laudos_amb
> WHERE codigo IN
> (SELECT grupos_tipos FROM tipo WHERE num_abs = 8)
>
>
> ---
> Rohtash Kapoor
> http://www.sqlmantra.com
>
>
>
> "Luiz Horacio" <lhoracio@iname.com> wrote in message
> news:uSK0$bWNFHA.3144@tk2msftngp13.phx.gbl...
(33010013,33010021,3
3010030,33010048,330
10056,33010064)[colo
r=darkred]
>
>[/color]

Sponsored Links