Need help on 2 column merge - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Need help on 2 column merge

Author

Topic

scrypto
Starting Member

3 Posts

Posted - 2004-09-10 : 04:58:00

Hello all,

I think this may simpler than I'm making it, but I just can't quite figure how to make this work.

I have a single table with two part_id's (part_id1, part_id2, connection_desc) and they can connect together. But a part's children are different depending on which viewpoint you have. So I need to merge the two columns where *either* of them contain a given parameter (@PartID), and then exclude the merged record that has @PartID.

Make sense?

Any help would be greatly appreciated - I've been up all night trying to figure out and searching and searching, and am just totally stuck. Thanks in advance.
-Arlo

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-09-10 : 05:03:00

Can you give some example data? What you have and what you want...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-10 : 05:04:22

a little...

select part_id1, part_id2,connection_desc
from yourtable
where part_id1 <> @PartID or part_id2 <> @PartID

is this what you're looking for?

scrypto
Starting Member

3 Posts

Posted - 2004-09-10 : 14:00:21

Ok here is an example:

tblPartConnections:
PartConnection_ID - Part_ID - PartConnectedTo_ID
1--1--2
2--2--3
3--1--3
4--1--4
5--5--1

So I want to find all conections (part_id or partconnectedto_id) with 1. So if I did some like this:

select partconnectedto_id, part_id from tblPartConnections
where part_id = 1 or partconnectedto_id = 1
union
select partconnectedto_id, part_id from tblPartConnections
where part_id = 1 or partconnectedto_id = 1

I then get:

partconnected_id - part_id
1--5
2--1
3--1
4--1

Now this is where I really need help: what I want to now merge those two resulting columns and exclude id 1 - so I'd end up with:

MergedPart_ID (or whatever)
5
2
3
4

Let me know if that doesn't clear it up. Thanks again.

-Arlo

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-09-10 : 14:10:48

I'd use something like:

SELECT CASE WHEN 1 = a.Part_ID THEN a.PartConnectedTo_ID ELSE a.Part_ID END AS OtherPart_ID
   FROM tblPartConnections AS a
   WHERE  1 IN (a.Part_ID, a.PartConnectedTo_ID)

-PatP

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-10 : 14:11:05

I THINK I understand what you are looking for:


select 
  case when part_id = 1 then PartConnectedTo_ID else Part_id as MergedPart_ID
from 
  tblPartConnections
where 
  1 in (part_id, partConnectTo_ID)

that where clause looks a little weird, but it is just shorthand for where partid=1 or partconnectToID = 1.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-10 : 14:11:37

WHOA !!!! Weird !!!!! almost word for word the exact same solution !! (Pat's solution, that is!)

Ehorn -- way too much work being done there! You must be a DBA !

- Jeff

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-09-10 : 14:14:30

quote:
Originally posted by jsmith8858

WHOA !!!! Weird !!!!! almost word for word the exact same solution !! (Pat's solution, that is!)

Ehorn -- way too much work being done there! You must be a DBA !
- Jeff

Just a slow Friday :) Was incorrect also :(

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-09-10 : 14:16:04

That's frightening!

-PatP

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-10 : 14:25:44

It's especially odd that we both used the rare "reverse IN clause" technique on our WHERE clauses.

I must say, Pat, that's a brilliant solution you've come up with !

- Jeff

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-09-10 : 14:29:15

I'm not sure that I'd go brilliant, but it could probably pass for elegant and I don't think anyone would argue about workable.

-PatP

scrypto
Starting Member

3 Posts

Posted - 2004-09-10 : 15:03:59

Indeed! Absolutely brilliant (I really must brush up on my conditional selecting, etc.!).

Thank you all very much for your input, this did the trick!

-Arlo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources