Solution I received from BACFUG member Jim Flannery:
This should work in MS-SQL:
SELECT sender = sentfrom.firstname + ' ' + sentfrom.lastname,
receiver = sentto.firstname + ' ' + sentto.lastname
FROM TableA
INNER JOIN TableB sentfrom ON TableA.From = sentfrom.PK
INNER JOIN TableB sentto ON TableA.To = sentto.PK
had to slightly modify it for MySQL, but it works like a charm!
> I know this is probably simpler than I'm making it, but I have Table A
> with two different "references" to the primary key in table B.
>
> Table A
> =======
> From To
> ==== ==
> 1 2
> 2 4
> 3 7
>
> Table B
> =======
> PK Firstname Lastname
> == ========= ========
> 1 John Doe
> 2 Jane Doe
> 3 John Smith
> 4 Jane Smith
> 7 Frank Jones
>
> I need to be able to show the first and last names from Table A's
> references to the PK in Table B (for both fields From and To).
>
> Basically, I'm trying to get the following output:
>
> Sent From Sent To
> ========== =======
> John Doe Jane Doe
> Jane Doe Jane Smith
> John Smith Frank Jones
>
> What kind of join do I need to make this work? Or does my table
> design not allow this? Regular inner joins don't seem to be working,
> and I'm certainly no SQL wiz.
>
> Any help is appreciated.
>
> MarieT
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322649
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
