1. Select A Field Twice With Different (special) Conditions

Hi guys,

My challenge is really 3 problems in 1.

I have Table1 and Table2 which are inner joined with an ID.
Each record in Table1 may have up to two corresponding values in Table2, which are Type and Name.

req1- I want to select Table2.Name twice (or more), with each selection conditioned on a specific Type.
req2- I also want all information pertaining to the same ID to be returned in each row
req3- I want all IDs to be returned, even if Table2.Name are empty for Type1 and/or Type2.

So, the source tables look something like this:

Table1.ID | ……
-----------------------
ID001
ID002
ID003
ID004

Table2.ID | Table2.Type | Table2.Name
--------------------------------------------------------
ID001 | Type1 | NameA
ID001 | Type2 | NameB
ID002 | Type1 | NameC
ID003 | Type2 | NameD


Ideally, my results should look like this:

Table1.ID | Table2.Name (Type=1) | Table2.Name (Type=2)
----------------------------------------------------------
ID1 | NameA for Type1 | NameB for Type2
ID2 | NameC for Type1 | (empty)
ID3 | (empty) | NameD for Type2
ID4 | (empty) | (empty)

Right now, I can only get Table2.Name to display once, and it won’t return the empty values, even with outer joins. The structure that I’m using is:

SELECT …
FROM …
WHERE Table2.Name IN
(SELECT Table2.Name
FROM Table1 INNER JOIN Table2 …
WHERE Table2.Name = ‘Type1’)

Sorry the abstractness, but this is the simplest way for me to express the problem.

Any help will be appreciated, thanks!