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.
| Author |
Topic |
|
akashenk
Posting Yak Master
111 Posts |
 Posted - 2007-05-18 : 16:14:51
|
| I have two tables that have the same number of fields, but the field data types do not necessarily match up..Table 1 is called "IDs" and it has a single field...Item_ID varchar(15)Table2 is called "Submissions" and has a single field... Submission_Count smallint)These tables have a one to one relationship, in that they will have the same number of records and each record in the IDs table corresponds to a record in the Submissions table. Furthermore, the order of the records matches up, so the 1st Id record, matches up with the 1st Submission record..so on and so forth, however, these tables aren't designed as relational database tables, in that the Submissions table obviously doesn't have a foreign key field that maps to the Ids table. The reason for this is that these tables are actually temp tables I create as part of a stored procedure. What I'm trying to accomplish is get a single temp table that has the combined records of the two tables. So, for the following sample dataIDs: Id1, Id2, Id3Submissions: 6, 27, 32I would like to create a single temp table that has the two fields in it and 3 recordsSo, the data would look like:Id1 6 Id2 27Id3 32I attempted to do a cross join with the Ids and Submissios tables, but this resulted in the following cartesion product table, which is obviously not what I want.Id1 6Id1 27Id1 32Id2 6Id2 27Id2 32Id3 6Id3 27Id3 32Can anyone lend me a hand in figuring out how to combine these two tables so that the result includes all the information and has the same number of records as the source tables? Thanks! |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-05-18 : 16:23:28
|
| when you create your temp tables, just also add an identity column to each. then just join your two temp tables on that identity column to get the results you need.- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
akashenk
Posting Yak Master
111 Posts |
Posted - 2007-05-18 : 17:20:32
|
| Ahhh. thanks. |
|
|
|
|
|
|
|
|
Cross Join?