SQL select - 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

Other Forums

MS Access

SQL select

Author

Topic

ymeyaw
Starting Member

3 Posts

Posted - 2006-03-27 : 10:38:42

hi,
Good day. I had a problem when selecting data from a table which look like this:

source target
a b
a c
b a
b c

what basic SQL statement i should use in order to :

1) select for two-ways data, with result like this:

source target
a b
b a

2) select data with only one way define in the table, with result look like this:

source target (1 way)
a c
b c

Thanks in advance!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-27 : 10:43:55

Is this what you want ?

declare	@table table
	(
		source	varchar(1),
		target	varchar(1)
	)
insert into @table
select 'a', 'b'	union all
select 'a', 'c'	union all
select 'b', 'a'	union all
select 'b', 'c'

select	b.*
from	@table a inner join @table b
	on	a.target	= b.source
	and	b.target	= a.source

select	b.*
from	@table a inner join @table b
	on	a.target	= b.source
	and	b.target	<> a.source

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

ymeyaw
Starting Member

3 Posts

Posted - 2006-03-27 : 10:55:12

hi tan,
thanks for that. I have only one table , and under the table , i have 2 columns, named source , and target. I need a query to run the selection from table below:

s=source, t= target

table
-----

s t
---
a b
a c
b a
b c

What i wish to do is to have a simple query where:
1)select data where they point to each other, and that result :
s t
---
a b
b a

2) select data where they only have one way defined, and result is:
s t
---
b a
b c

thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-27 : 10:58:52

i am only using one table. a and b are table alias

ymeyaw
Starting Member

3 Posts

Posted - 2006-03-27 : 11:59:52

hi,
i tried using simple query based on ur example given, but filtering data with one way defined in the table doesnt work out probably.
Below is my query:

SELECT b.[source], b.[target]
FROM table AS a INNER JOIN table AS b ON (b.[target]<>a.[source]) AND (a.[target]=b.[source]);

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-27 : 20:37:44

I just noticed that the expected result that you specify in your first post and 2nd post are different

From 03/27/2006 : 10:38:42

quote:
2) select data with only one way define in the table, with result look like this:

source target (1 way)
a c
b c

From 03/27/2006 : 10:55:12

quote:
2) select data where they only have one way defined, and result is:
s t
---
b a
b c

The code i posted will return the result as specify in your 1st post.

declare	@table table
	(
		source	varchar(1),
		target	varchar(1)
	)
insert into @table
select 'a', 'b'	union all
select 'a', 'c'	union all
select 'b', 'a'	union all
select 'b', 'c'

select	b.*
from	@table a inner join @table b
	on	a.target	= b.source
	and	b.target	= a.source
order by b.source, b.target

source target 
------ ------ 
a      b
b      a

(2 row(s) affected)

select	b.*
from	@table a inner join @table b
	on	a.target	= b.source
	and	b.target	<> a.source
order by b.source, b.target

source target 
------ ------ 
a      c
b      c

(2 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources