how to join two tables without a key?? Possible? 1

[WestSide2003]

Hi,

I have two tables that I would like to Join.

Basically I need to retrieve UserID, Password from Table A.

I also need to get FirstName, LastName from Table B.

I know it would be obvious to store them in 1 table, but they are not in the same table.

How can I write a query that retrieves the required fields? I do not have any FK in Table A that would allow me to create a relationship...

Table A

UserID
Password

Table B

FirstName
LastName

Any help appreciated

 

[AshleyRickards]

How are the usernames formated? If they used lastname first initial or something like that you could link on on part of a string.

Ashley L Rickards
SQL DBA

 

[nigelrivett]

Looking at the tables there doesn't seem to be any way to define the requirement.

If you take an entry from tableA how do you know which is the corresponding entry from Table B?

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[WestSide2003]

So to make this work we would need to insert a FK?

Thanks..

 

[nigelrivett]

You would usually have the UserID on both tables or define another unique field on one table and include it on the other if you want userID to be updateable.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[EddieBeaux]

If the UserID is something predictable you can make a key out of the first and last name. For example, if the first and last name are "John Smith" and the UserID is "JSMITH", you can concatenate the first initial and the last name and join that new field to the user id in the password table:

Select U.FirstName, U.LastName, P.UserID, P.Password
From [Table B] U Inner Join [Table A] P
On Left(U.FirstName, 1) + U.LastName = P.UserID