Set Theory + IT question

homeryansta

Give me some ideas IT guys. I'm stuck trying to figure this out on a Sunday!!!

Given set A and set B where (A != B)

Set A is given in comma delimited form:
ID,Value
---------
1,1
1,2
2,2
3,1

Set B is given in comma delimited form:
ID,Value2
----------
1,1
1,3
1,4
2,2
3,1
3,2

I want the result set of A and B merged to look like this

ID,Value,Value2
------------------
1,1,1
1,2,3
1,null,4
2,2,2
3,1,1
3,null,2

Notice that I want to join the ID in the order they are displayed.
If I do a "full outer join" on the ID, I get 6 results for ID 1 which is not what I want.
If I do a union on both sets with dummy columns to match them up, I will still get 5 results for ID 1.

Any ideas? I can barely think anymore.



The IDs have to match, but doesn't cross match like in a traditional join.

Example:
if A has 3 rows with ID 1
and B has 1 row with ID 1

match the first row in A to the only row in B
and then just display the rest of A.

In a traditional join, all 3 rows in A would be join to the single row in B.

__________________

XiP

Are you trying to do this in SQL?

homeryansta

yes, SQL server

__________________

XiP

You need to do a right outer join on the ID

homeryansta

That won't work if the left set has data that doesn't exist in the right set.

The result I want is a 1-1 relationship where the matching only happens in order

example:

if A{1,1,1,2,2,3,3,3} and B{1,1,2,4}

A B
-- --
1 1
1 1
1 null
2 2
2 null
3 null
3 null
3 null
null 4

__________________

Little Blue

This is with MS Access SQL, but SQL Server should be very similar. I struggled with this a lot the first time I needed to do it years ago. The trick is to build a set of all master combinations with a UNION, then use that as the base for the Left Joins.

SELECT x.Id, a.Value, b.Value
FROM ((
SELECT Id, Value
FROM A
UNION
SELECT Id, Value
FROM B
) as x
left join a on x.id = a.id and x.value = a.value)
left join b on x.id = b.id and x.value = b.value

Id a.Value b.Value
1 1 1
1 2
1 3
1 4
2 2 2
3 1 1
3 2

homeryansta

That won't work. The reason is because the solution I want is a 1-1 relationship between a m-m set.

Here is how I solved it.

I partitioned the IDs and assigned unique keys(SubID) to them..

That is:

ID,Value2,SubID
----------
1,1,1
1,3,2
1,4,3
2,2,1
3,1,1
3,2,2

I do the same thing to the other set.

The resulting set after I do a full outer join on ID and SubID is exactly what I wanted.

Thanks for the help folks!

__________________

6MT

__________________
Andrew

2009 370Z Sport/Touring 6MT sold
2013 Honda Civic Si coupe D/D

Staggered357

WTF squared..
My head hurts just looking at it... My hats goes off to you programmers... I will stick with the technical support and subnetting

6MT

Ahh.... a cable swapper, are ya?

__________________
Andrew

2009 370Z Sport/Touring 6MT sold
2013 Honda Civic Si coupe D/D

homeryansta

we need guys like you to break the network so we have down times.

__________________

Staggered357

Definitely!!!

hansoac

I started out my degree as CS, but ended up changing to MIS because I could not stand sitting and reading/writing code all day. However, as I age I started getting back into some programming and seem to enjoy it.

I am in an iOS Objective-C class right now, tough stuff!

homeryansta

sitting, reading, writing all day? What kind of cs ******** program did your school have? I would think MIS is more of the worthless sitting, reading, writing you mentioned.

I was programming games with AI, 3D graphics at a very low level, rewriting the memory management system in an OS. Things that are actually interesting.

__________________