Multiple joins to the same table?

[TomLeMes]

I am using SQL pass through to get selections of data from my SQL2000 server. My general approach involves making fairly hefty selections, usually with quite a few joins. If we take the example of an invoice table, it has plenty of fields that I can display directly and then quite a few where I need to link the fk to the static data tables. For instance we have a field to show who created the invoice fk_user_create and that can be linked to like this:

SELECT invtable.pk, invtable.invvalue, invtable.invdate, invtable.fk_user_create, users.username 
FROM invtable 
JOIN users on invtable.fk_user_create = users.pk

I execute this with an SQLEXEC command and that works fine. I can bind my VFP control to the joined username field and that's fine. I can then use my 'nextrecord' and 'prevrecord' buttons to move between invoices and the username shows accordingly for each invoice.

However, I actually have several other fields I want to show, including the user that last amended the invoice (fk_user_amend) and the user that deleted it (fk_user_del - just examples). The 3 fk fields in my invoice table all link to the one user table but clearly the chances are that there will be 3 different usernames returned. Is there a way to deal with this in a single selection? I guess what I'm trying to avoid is having to re-do the lookups to the user table every time I move between records.

Thanks, Tom

 

[bborissov]

No problem to join one table many times:

SELECT invtable.pk, invtable.invvalue, invtable.invdate, invtable.fk_user_create, users.username,
invtable.fk_user_deleted, delusers.username
FROM invtable
JOIN users on invtable.fk_user_create = users.pk
JOIN users AS DelUsers on invtable.fk_user_deleted = DelUsers.pk
etc.

Borislav Borissov

 

[TomLeMes]

A nice easy one! Thanks very much Borislav!

 

[bborissov]

you are welcome

Borislav Borissov