Problem with view resultset - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Problem with view resultset

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2007-11-12 : 06:48:17

How can I limit the resultset returned from an inner join?
I have:
tableA tableB
------ ------
1 1
1 2
2 1
2 2
3 1
3 2
3 3
... ...

I want the resultset to look like this:
tableA tableB
------ ------
1 1
2 1
3 1
... ...

tableA is the table that should limit the resultset. I have tried distinct, group by and havin without any luck.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-12 : 06:49:24

http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

maevr
Posting Yak Master

169 Posts

Posted - 2007-11-12 : 07:32:40

Thanks for the fast reply!
I have read and tried but cannot get it to produce the result I want.
I get multiple results from the inner select, how do I get past that?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-12 : 07:54:20

show us what you tried.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

maevr
Posting Yak Master

169 Posts

Posted - 2007-11-12 : 08:12:51

CREATE TABLE [dbo].[test1](
[a1] [int] NULL,
[a2] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[test2](
[b1] [int] NULL,
[b2] [int] NULL
) ON [PRIMARY]

SELECT a1, b2
FROM test1 inner join test2 on exists (SELECT * FROM test2 WHERE test1.a1 = test2.b1)

Resultset
1 1
1 3
1 1
2 1
2 3
2 1
3 1
3 3
3 1

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-12 : 08:16:52

wow! that's the first time i've seen this kind of syntax. that's like .... amazing!! didn't even think that works.

try:
select a1, min(b2) as b2
from test1
inner join test2 on test1.a1 = test2.b1
group by a1

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 08:18:12

You haven't told us yuor business rules very well.
Frankly speaking, you haven't told us at all.

So we keep geussing and guessing...

-- Peso 1
SELECT		t1.Col1,
		MIN(t2.Col2)
FROM		Table1 AS t1
INNER JOIN	Table2 AS t2 ON t2.Col1 = t1.Col1
GROUP BY	t1.Col1

-- Peso 2
SELECT DISTINCT	t1.Col2,
		1
FROM		Table1 AS t1
WHERE		EXISTS (SELECT * FROM Table2 AS t2 WHERE t2.Col1 = t1.Col2

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources