Mapping IDs using a join? 1

[ethorn10]

Hello,

Hopefully I can explain this without typing too much or getting confused myself. What I need/want is a select statement that returns two columns, one of which has the IDs based on one set of criteria and the other is IDs based on another set of criteria. So for example, I want to use Table1 and get ExampleID where OtherID = 1 and OtherID = 2.

Table1
-------
ExampleID     OtherID
   25            1
   26            1
   27            1
   30            2
   31            2
   32            2

So I want my result set to look like:

ExampleID     Expr1
   25           30
   26           31
   27           32

I tried using a cross join and that was quite close to what I wanted only the whole cartesian idea got in the way (since there were 3 in each, I got a result set of 9 with incorrect mappings).

Thanks for the help...

 

[gmmastros]

How do you know that 30 should be returned on the same row as 25? There must be some other criteria that I'm missing here.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ethorn10]

I actually don't really care where they are mapped. I guess one thing I forgot to mention was that there will always be a perfect match so we don't have to worry about that. So 25 could have 31 or 32 mapped to it as long as the rest didn't.

 

[gmmastros]

Here is some code to get you started. I Hope it helps.

-- Setup the temp data for testing
Declare @Table1 table(ExampleId Integer, OtherId Integer)
Insert Into @Table1(ExampleId, OtherID) Values(25,1)
Insert Into @Table1(ExampleId, OtherID) Values(26,1)
Insert Into @Table1(ExampleId, OtherID) Values(27,1)
Insert Into @Table1(ExampleId, OtherID) Values(30,2)
Insert Into @Table1(ExampleId, OtherID) Values(31,2)
Insert Into @Table1(ExampleId, OtherID) Values(32,2)

-- Now do the work
Declare @FirstIds Table (RowId Integer Identity(1,1), ExampleId Integer)
Insert 	Into @FirstIds(ExampleId)
Select 	ExampleId As FirstId From @Table1 Where OtherId = 1

Declare @SecondIds Table (RowId Integer Identity(1,1), ExampleId Integer)
Insert 	Into @SecondIds(ExampleId)
Select	ExampleId As SecondId From @Table1 Where OtherId = 2

Select	FirstTable.ExampleId As FirstId,
		Second.ExampleId As SecondId
From	@FirstIds FirstTable
		Inner Join @SecondIds Second on FirstTable.RowId = Second.RowId

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ethorn10]

Thanks. I was really hoping it could be accomplished without temporary tables. Apparently that isn't possible?

Thanks again though gmmastros

 

[MartDawg]

I have two tables, when I join them, my recordset comes back like this...

field1, field2, field3
stuff, stuff, type1
stuff, stuff, type2


Is it possible instead of returning the duplicate data(field1-2)...I would like the data like this

stuff, stuff, type1
, type2

 

[ethorn10]

I did end up using gmmastros' idea of temporary tables so...a star for you.

Thanks for the help.

 

[gmmastros]

Thank you for the star.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom