MS Query - query to return value based on two joins within the same tables

Hello All.

I'm trying to create a Microsoft Query on Excel that connects to an external database and was having a SQL question on how to return query records based on information below.

Although it's in excel, I believe this is more of a general SQL question. I apologize and thank you for the long read in advance!

Below are 2 Table structures that only include fields that are relevant to this question.

*==========*
| Transaction |
*==========*
TransactionID (Unique)
EntityID
ForEntityID

*=======*
| Entity |
*=======*
EntityID (Unique)
Name

Using the example table structures from above, I currently have a microsoft query which returns all records from [Transaction] Table and also join the [Entity] Table by (Entity.EntityID = Transaction.EntityID) so that it returns on record row the name of the Entity as one of the columns.

I am having trouble understanding how I may be able to achieve the next task.

The values of 'Transaction.EntityID' and 'Transaction.ForEntityID' are both consisting of the IDs that match the Unique ID of 'Entity.EntityID'.

While most records in [Transaction] Table have empty values for 'Transaction.ForEntityID', Some records do contain values in both 'EntityID' and 'ForEntityID'

For those records in [Transaction] where there is value in 'Transaction.ForEntityID', I want to append another column in the query that shows the name of the entity that the record is being entered "For" (the value in 'Entity.Name').

The problem however, is that I already have a join on (Entity.EntityID = Transaction.EntityID). It wouldn't be logically possible to add another join (Entity.EntityID = Transaction.ForEntityID) is this correct?

Simply put, what I'm hoping to achieve is:

If there's a value in 'Transaction.ForEntityID', I want to append a column in the query that returns the matching value from Entity Table (Entity.EntityID = Transaction.ForEntityID) but if there is no value in 'Transaction.ForEntityID', I'd like that query column to return the matching value from Entity Table (Entity.EntityID = Transaction.EntityID)

For Example:

*=================*
| Transaction Records |
*=================*
TransactionID - EntityID - ForEntityID
100 - 1 - (blank)
101 - 1 - 2
102 - 2 - (blank)

*============*
| Entity Records |
*============*
EntityID - Name
1 - Albert
2 - Brian

On the query, How can it be structured so that the query record are returned as follows :
ID: 100, EntityID: 1, ForEntityID: , EntityName: Albert
ID: 101, EntityID: 1, ForEntityID: 2, EntityName: Brian
ID: 102, EntityID: 2, ForEntityID: , EntityName: Brian

I'm sorry for not the most efficient way of asking the question due to my limited knowledge in this matter. Hope you understand, and I appreciate any help!

Hi,
I think I got your need, will try to explain my idea.
First part of code (before Union) will return all records where Entity.EntityID = Trans.EntityID AND Trans.ForEntityID is empty.
Result should be:
ID: 100, EntityID: 1, ForEntityID: , EntityName: Albert
ID: 102, EntityID: 2, ForEntityID: , EntityName: Brian

Union means that we want to add something to first return. Using Union - same columns might be selected, but criteria can be different.
So now we will select exactly the same columns, BUT note that now Trans.ForEntityID = Entity.EntityID and we are interested only in records where Trans.ForEntityID is not empty.
Result should be:
ID: 101, EntityID: 1, ForEntityID: 2, EntityName: Brian

Final output will combine those two selection in one table.
In Microsoft Query find button "SQL" and paste there code you see below, but be sure that all column names and table names are valid.

Please Login or Register  to view this content.

Thank you so much alansidman and kasan!

Kasan's suggestion with UNION had gotten me very close to what I was looking to achieve and I thank you greatly for that suggestion. This new query however is still returning less rows than before, so I'm currently in the midst of trying to figure out why those missing records are being left out.

Thanks! again and will report back with any findings.

Thanks for feedback.
Result is less rows than expected? Union will not return duplicate rows, if you need all records - use UNION ALL.
And may be you could give us small sample of your data + your query code?

So the issue for missing rows (and I believe this issue goes a bit outside the scope of the original inquiry) was that by having "WHERE Entity.EntityID = Trans.EntityID" or "WHERE Entity.ForEntityID = Trans.EntityID" , it was not returning the records from the Trans table where Trans.ID was blank or ''.

Now, I tried modifying

WHERE Entity.EntityID = Trans.EntityID AND another criteria AND another criteria

to

WHERE (Entity.EntityID = Trans.EntityID OR Trans.EntityID = '') AND another criteria AND another criteria

but now it's returning all rows from both tables.

Thanks for help, I tried to solve it for some months...

" it was not returning the records from the Trans table where Trans.ID was blank or '' "
" WHERE (Entity.EntityID = Trans.EntityID OR Trans.EntityID = '') AND another criteria AND another criteria "
Does this mean that Trans.EntityID can be blank in your table? Like:
*=================*
| Transaction Records |
*=================*
TransactionID - EntityID - ForEntityID
100 1 (blank)
101 1 2
102 2 (blank)
103 (blank) (blank)

Originally Posted by kasan

" it was not returning the records from the Trans table where Trans.ID was blank or '' "
" WHERE (Entity.EntityID = Trans.EntityID OR Trans.EntityID = '') AND another criteria AND another criteria "
Does this mean that Trans.EntityID can be blank in your table? Like:
*=================*
| Transaction Records |
*=================*
TransactionID - EntityID - ForEntityID
100 1 (blank)
101 1 2
102 2 (blank)
103 (blank) (blank)

Yes sir.

But still would like that record TransactionID=103 to be retrieved in the query.

to post #8
How about:

Please Login or Register  to view this content.

Originally Posted by kasan

to post #8
How about:

Please Login or Register  to view this content.

Ohh.. so i need to connect 2 queries with union (and with that added to the original inquiry), it looks like I have to connect 4 queries with union? I'll give that a try and report back. Thanks!