inner join with several fields...
-
Is it possible to JOIN two tables by combining two columns in one table into a "key" and join it to the other tables "key".
Something like this:
begin tran
CREATE TABLE #users(id INT PRIMARY KEY, selection INT, period INT)
INSERT #users VALUES (1, 1, 1)
INSERT #users VALUES (2, 1, 2)
INSERT #users VALUES (3, 1, 3)
INSERT #users VALUES (4, 1, 4)
CREATE TABLE #user_files(id INT PRIMARY KEY, userid INT, number INT)
INSERT #user_files VALUES (1, 1, 11)
INSERT #user_files VALUES (2, 2, 12)
INSERT #user_files VALUES (3, 3, 13)
INSERT #user_files VALUES (4, 4, 14)
select cast(selection as varchar(1))+cast(period as varchar(1)) as number from #users
rollback
I would like to join #users.number=user_files.number and #users.id=#user_files.userid aswell, is that possible?
Ty for your time
-
I don't see a column #users.number in your definition shown above.
-
You are right, its nonexistant, i want to combine two columns selection and period into as number column
select cast(selection as varchar(1))+cast(period as varchar(1)) as number from #users
join.png:
-
Something like this:
CREATE TABLE #users(id INT PRIMARY KEY, selection INT, period INT)
INSERT #users VALUES (1, 1, 1)
INSERT #users VALUES (2, 1, 2)
INSERT #users VALUES (3, 1, 3)
INSERT #users VALUES (4, 1, 4)
CREATE TABLE #user_files(id INT PRIMARY KEY, userid INT, number INT)
INSERT #user_files VALUES (1, 1, 11)
INSERT #user_files VALUES (2, 2, 12)
INSERT #user_files VALUES (3, 3, 13)
INSERT #user_files VALUES (4, 4, 14)
SELECT
*
FROM
[#users] u
INNER JOIN [#user_files] uf
ON (u.[id] = uf.[userid]
AND CAST(CAST(u.selection AS varchar) + CAST(u.period AS varchar) AS INT) = uf.[number])
;
DROP TABLE [#users];
DROP TABLE [#user_files];
-
Interesting, think it might work, ill give it a shot!
Thanks! 🙂
-
If the range of values for u.selection and u.period are constrained to single digit integers [1-9], why not skip the cast as varchar/int parts and just work with integer values, like this...
SELECT
*
FROM
[#users] u
INNER JOIN [#user_files] uf
ON (u.[id] = uf.[userid]
AND ((u.selection * 10) + u.period) = uf.[number])
;
If the range of values is NOT constrained to single digit integers, I don't know how either method would work reliably.
Rob Schripsema
Propack, Inc. -
Rob Schripsema (4/16/2012)
If the range of values for u.selection and u.period are constrained to single digit integers [1-9], why not skip the cast as varchar/int parts and just work with integer values, like this...SELECT
*
FROM
[#users] u
INNER JOIN [#user_files] uf
ON (u.[id] = uf.[userid]
AND ((u.selection * 10) + u.period) = uf.[number])
;
If the range of values is NOT constrained to single digit integers, I don't know how either method would work reliably.
Hard to say based solely on the sample data. For example, the actual data could have 14, 12 in the user table and 1412 in the user_files table. Simply multiplying by 10 wouldn't work, would it?
-
Great suggestion, luckly they are constrained to 1-4.
-
Hmm is it an absolute must to use the virtual tables u and uf?
-
Lynn,
You're absolutely correct. There are quite a few unknowns here.
But I made qualified assumption based on the OP's initial post, which said,
select cast(selection as varchar(1))+cast(period as varchar(1)) as number from #usersRob Schripsema
Propack, Inc. -
memymasta (4/16/2012)
Hmm is it an absolute must to use the virtual tables u and uf?Those are table alaises, and I highly recomment using them. In fact, I should have specified each column in the select list using the table alais.column name instead of using the *.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply