Need Help with some SQL code 2

[pradchal1]

Need help to produce two SQL queries on the following situation:

I have a table that looks like this:

Column A Column B

A B
C D
E F
G H
B A
D C
F E
H G


These two columns are co-requisite of each other. In other words, A is a co-requisite of B, and B of A.

I need to produce two tables that would look like this:

Table1

Column A
A
C
E
G

Table2

Column A
B
D
F
H

Any kind of help would be grately appreciated. Thanks

 

[johnv20]

Not sure I fully understand you are you saying that you want to populate table 1 with the values from Col A where Col A in the source table has values of A,C, E & G only and similary for Table 2 ?

 

[pradchal1]

What I am trying to do is, seperate the co-requisites.
If A is a co-requisite of B, and C is of D,
I want A and C in one table and B and D in another.

Let me put my example in a different way:

Let's say I have a table of married couples.

Table Married Couple:

SpouseA SpouseB

Jim Mary
Jim Kim
Jack Susan
Mark Wendy
Mary Jim
Kim Jim
Susan Jack
Wendy Mark

Note: Jim is married twice.

Now I want two tables where the couples are seperated:

SpouseA

Jim
Jack
Mark

SpouseB

Mary
Kim
Susan
Wendy

Please let me know if this is not clear enough.

 

[JamesLean]

If each pair of values is duplicated in the source table, just with the order reversed, how do you know which value should go in the first table and which in the second?

--James

 

[pradchal1]

We don't know which one goes to which table and it also does not matter which one goes where, as long as we can seperate the spouses.

 

[johnv20]

This is very messy - there must be an easier way to do it but I can't just think of it now

declare @tmpName varchar(20)
declare Names cursor LOCAL FORWARD_ONLY READ_ONLY for
        select distinct Name 
        from Source
        where Name not in select NameCol from TableB
open Names
fetch next from Names into @tmpName
 while @@FETCH_STATUS=0
  begin
  insert TableA values (@tmpName)
  insert TableB select ColB from Source where Name = @tmpName

fetch next from Names into @tmpName
  end
close Names
deallocate Names

 

[JamesLean]

You might find this easier:

INSERT tableA
SELECT colA
FROM source
WHERE colA > colB

INSERT tableB
SELECT colA
FROM source
WHERE colA < colB

--James

 

[JamesLean]

Also, if you have duplicates, as per your second example, then just use SELECT DISTINCT on those two queries.

--James

 

[pradchal1]

Thanks a lot JamesLean. You made it look so easy. And thanks to all of you who tried to help. Thanks a lot.

 

[ookete]

Wow, I have to give you a star James. That is so smooth and simple... I would not have thought to just do an inequality comparison like that.