SQL - Join two fields in the same table to a single field in another table

sql

Solution

Left Join Contacts with Lookup twice. Once with Group_1 and once with Group_2. Left Join instead of just Inner Join, as you may have a contact without two groups.

SELECT C.Name,
       G1.Lookup_Name,
       G2.Lookup_Name
FROM   Contacts C
       LEFT JOIN Lookup G1 ON G1.Lookup_Id = C.Group_1
       LEFT JOIN Lookup G2 ON G2.Lookup_Id = C.Group_4

Problem

I'm working on a way to lookup the description for a code that appears in two fields of the same table. The table/field names are : ``` Contacts Name, Group_1 and Group_4 Lookup Lookup_Id, Lookup_Name ``` `Contact.Group_1` and `Contact.Group_4` both refer to values in `Lookup.Lookup_Id` and need to be resolved to their corresponding name values in `Lookup.Lookup_Name`. How can I connect both fields to the `Lookup` table and have them bring back their respective `Lookup_name` values ?

Original source