Can I retrieve fields for 2 different IDs

Question

The title is abit too vague so let me explain more:

I have a lot of table joins that retrieves data according to my select, for example my SELECT first_name, last_name retrieves "Adam", "Tedding", it only retrieves this from the INNER JOIN Users ON Users.ID = Offers.buyer_id) in my query, I have another field in my Cars table called "owner_id" is it possible to do another INNER JOIN which retrieves the first_name and last_name of the owner_id rather than just the buyer_id's first_name and last_name

This is my query:

SELECT Users.id, Users.first_name, Users.last_name
FROM (Offers
INNER JOIN Users ON Users.ID = Offers.buyer_id)
INNER JOIN Cars ON Cars.ID = Offers.car_id)
WHERE Cars.owner_id = 44

To explain further, all this does is gets the buyer_id's first_name and last_name, I want to get the owner_id's first_name and last_name also

I am also using Microsoft Access (If this helps)

Yes, but you need to join back to users once for the owner, once for the users. and alias both tables... but where the heck is the "CARS" table in your example? — xQbert
@xQbert Sorry I deleted the unnecessary lines from the query and accidentally deleted the Cars inner join but I have updated the question! — c0mrade

MrTheWalrus MrTheWalrus · Accepted Answer · 2015-04-17T17:46:49

To join a table multiple times (which is, I think, what you're trying to do), you'll need to alias it.

Aliasing in Access: https://support.office.com/en-za/article/Access-SQL-FROM-clause-2a7d031c-6912-4e8c-a644-590940ddaed1#bm2

You'll probably end up with something like the following (though be warned, I haven't actually used Access, and am relying on general SQL knowledge, so the precise syntax may be different):

SELECT Users.first_name, Users.last_name, Owners.first_name, Owners.last_name
FROM Offers
INNER JOIN Users ON Users.ID = Offers.buyer_id
INNER JOIN Users AS Owners ON Owners.ID = Cars.owner_id

Can I retrieve fields for 2 different IDs

1 Answers