Combined Left Joins

Tfa · Jan 30, 2017

Hello yet again
here is my problem
i have two tables
one is Clients the other is Destinations
in Clients i have ID , name and DEST1,DEST2,DEST3
in Destinations i have to fields DESTID and description
now the thing i want to do is
is after each DEST to get the maching description
something like
select A.ID, A.name, A.DEST1, (B.description) as Descrition1 ,A.DEST2 ,(b.description) as description2 ,A.DEST3, (B.description) as Descrition3
any ideas ???

Grumm · Jan 30, 2017

So you have 3 columns in the client table ? What if next week the boss says to add a new destination ? Then what ?

I think that is also why you have trouble to do a normal left join.
Best way is to remove the redundant information. (extra table maybe)

Tfa · Jan 30, 2017

well i can't do that
so what im trying to do is a multiple inner join with the same table

Grumm · Jan 30, 2017

If that isn't an option, then you must create a query like this :

Code:

select A.ID, A.name,A.DEST1,B.description,A.DEST2,C.description,A.DEST3,D.description from Clients as A 
         join Destinations as B on A.DEST1 = B.DESTID 
         join Destinations as C on A.DEST2 = C.DESTID 
         join Destinations as D on A.DEST3 = D.DESTID

So for each column in client, you must do a join to the destination table.

Uncle Gizmo · Jan 30, 2017

Tfa said:
well i can't do that

And you don't have to. But Grumm has quite correctly explain to you the normal way to do it.

If you follow the normal practice then you will be following a tried and tested route. However if you "can't do it" then you might well find yourself facing problems which have no recognised solution. You will quite possibly be able to solve these problems with some complicated VBA code however you won't find much readily available.

So if you are pretty good at VBA go for it however if not you might like to reconsider the advice.

Sent from my SM-G925F using Tapatalk

Tfa · Jan 30, 2017

well although i found the anwser to another forum thank you for replying
the problem is that this is the company database that i work on and i wasn't the one to design it
also you need () or it propubly won't work

select A.ID, A.name,A.DEST1,B.description,A.DEST2,C.description,A.DEST3,D.description from(( Clients as A
join Destinations as B on A.DEST1 = B.DESTID)
join Destinations as C on A.DEST2 = C.DESTID)
join Destinations as D on A.DEST3 = D.DESTID

Grumm · Jan 30, 2017

Maybe you can ask for a more efficient design of the database... And get a nice bonus for the effort.
Problem now is that if something changes, you will spend more time to read all the code again to include the new parts. (Extra joins and selects)

Combined Left Joins

Tfa

Registered User.

Grumm

Registered User.

Tfa

Registered User.

Grumm

Registered User.

Uncle Gizmo

Nifty Access Guy

Tfa

Registered User.

Grumm

Registered User.

Similar threads

Users who are viewing this thread