Joining queries that get values from the other

Question

I am trying to run the following MS Access query using values from the first select into the second: (This question originated from here Using results from a query within the same query but I changed it up greatly and I feel it warrants a new question)

SELECT 
A.field1,
A.field2,
A.field3,
FROM TheTable AS A
Where A.Id = 3;
?Join / UNION?
SELECT
B.field4,
B.field5
FROM TheTable AS B
Where B.Id = field1

In the end, I'd like the there to be 5 columns as output based on these two queries, but how can I do the joins/unions correctly for this? Note that TheTable is the same in both queries but I am using it differently in each part...

I wouldn't mine having a first row of 3 elements the next row of 2 columns (and a null). As long as I can get it done in one query.

So the output would look like:

A.field1, A.field2, A.field3, B.field4, B.field5
1, 2, 3, 4, 5

etc.

Answer 1

A join will only work if there are the same number of rows generated from each query or one will always generate fewer rows.

Barring that, you can get this output from one query or a union of two queries.

The union is actually simpler and easier to maintain:

SELECT  A.field1, A.field2, A.field3, null Field4, null Field5
FROM    TheTable AS A
Where   A.Id = 3
union all
SELECT  null, null, null, B.field4, B.field5
FROM    TheTable AS B
Where   B.Id = field1;

The single query is more efficient, but it's also slightly more complicated:

SELECT  case id when 3 then field1 else null end as Field1,
        case id when 3 then field2 else null end as Field2,
        case id when 3 then field3 else null end as Field3, 
        case id when 3 then null else field4 end as Field4, 
        case id when 3 then null else field5 end as Field5
FROM    TheTable AS A
Where   Id = 3
    or  Id = Field1;

However, the main question you have to answer may NOT be efficiency over maintainability. The one thing you have to consider is if Field1 could ever equal 3. If that should ever be the case, then the same row will generate a tuple in the "A" query and the "B" query in the union. However, as written, it will only generate one tuple with the Field1, Field2 and Field3 values and NULL in Field4 and Field5 fields in the single query.

So if that should ever be possible (Field1 = ID), you must choose which query generates the best outcome for your needs. If that condition is not possible, then choose either one.

Answer 2

I think what you want is

SELECT 
A.field1,
A.field2,
A.field3,
B.field4,
B.field5
FROM TheTable  A inner join  TheTable AS B on
B.Id = field1
Where A.Id = 3