Double Join`ted 1

[SQLScholar]

Hey all,

I have a bit of a bizaar query. Please dont try and ask the reasoning behind this - its just sorting out some errors.

If i am doing a select statement which joins a few tables and ends up with something like below

LenderAref LenderBref Client
3 2 bob
2 10 chris
10 4 dave

So basically what its doing is finding records in different tables where the lenderRef is different (should be the same).

Now what i want is to be able to get the name off the lender table. Now how in an SQL select query would i define that i want the lender name for lenderAref, and then lenderBref.

hope this makes sense.

D

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[PHV]

If i am doing a select statement
Why not posting the sql code if you want some help on it ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KenReay]

Hi

Make a query including your existing query, add the Lender name table Twice (access will prefix second one with _1), make a joint from LenderA to one instance of the Lender table, and from B to the other instance, drag whichever columns from the two instances you want into the query grid

Voila

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[hneal98]

So, in your example above, what are you looking for?

 

[SQLScholar]

I think that makes sense ken - i will have a go. An SQL example might be better if anyone has one to hand.

I can post the code if you wish - the reason i didnt was that it is much more complex then the above, and i thought a simplified question would be much easier.

Here is the code

SELECT
	`Policy Details`.`Policy Record Number`,
	first(`Policy Details`.`Client Counter`),
	first(`Basic Client Details`.`Introducer Number`) as `basic intro number`,
	first(`Introducer_Accounts`.`Introducer Record Number`) as `introducer_Acc_intro number`,
	first(`Basic Client Details`.`Insured Name`)
FROM
	(`Policy Details` 
INNER JOIN 
	`Introducer_Accounts` 
ON 
	`Policy Details`.`Policy Record Number` = `Introducer_Accounts`.`Policy Record Number`) 
INNER JOIN 
	(`Basic Client Details` 
INNER JOIN 
	`Introducer Details-Scheme` 
ON 
	`Basic Client Details`.`Introducer Number` = `Introducer Details-Scheme`.`Introducer Number`) 
ON 
	`Policy Details`.`Client Counter` = `Basic Client Details`.`Client Counter`

WHERE

`Basic Client Details`.`Introducer Number`<>`Introducer_Accounts`.`Introducer Record Number`

group by `Policy Details`.`Policy Record Number`

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[SQLScholar]

anyone got any ideas?

D

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[PHV]

What are LenderAref and LenderBref in your real query ?
You want to retrieve what from where ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLScholar]

PHV

`Basic Client Details`.`Introducer Number`
AND
`Introducer_Accounts`.`Introducer Record Number`
are both introducer numbers.

What i then want to do is match each of these numbers with the `Introducer Details-Scheme`.`Introducer Number` and return introducers name for each one.

Thanks for your time

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[PHV]

Something like this ?
SELECT
`Policy Details`.`Policy Record Number`,
first(`Policy Details`.`Client Counter`),
first(`Basic Client Details`.`Introducer Number`) as `basic intro number`,
first(IDSCli.`Introducer Name`) as `basic intro name`,
first(`Introducer_Accounts`.`Introducer Record Number`) as `introducer_Acc_intro number`,
first(IDSAcc.`Introducer Name`) as `introducer_Acc_intro name`,
first(`Basic Client Details`.`Insured Name`)
FROM
`Policy Details`
INNER JOIN
(`Introducer_Accounts`
INNER JOIN
`Introducer Details-Scheme` AS IDSAcc
ON
`Introducer_Accounts`.`Introducer Record Number` = IDSAcc.`Introducer Number`)
ON
`Policy Details`.`Policy Record Number` = `Introducer_Accounts`.`Policy Record Number`
INNER JOIN
(`Basic Client Details`
INNER JOIN
`Introducer Details-Scheme` AS IDSCli
ON
`Basic Client Details`.`Introducer Number` = IDSCli.`Introducer Number`)
ON
`Policy Details`.`Client Counter` = `Basic Client Details`.`Client Counter`
WHERE
`Basic Client Details`.`Introducer Number`<>`Introducer_Accounts`.`Introducer Record Number`
group by `Policy Details`.`Policy Record Number`

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLScholar]

i am getting a syntax error - in this bit

`Policy Details`.`Policy Record Number` = `Introducer_Accounts`.`Policy Record Number`
INNER JOIN
(`Basic Client Details`
INNER JOIN
`Introducer Details-Scheme` AS IDSCli
ON
`Basic Client Details`.`Introducer Number` = IDSCli.`Introducer Number`)

I tried having a look through but its really losing me.

Thanks

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[PHV]

And something like this ?
SELECT
`Policy Details`.`Policy Record Number`,
first(`Policy Details`.`Client Counter`),
first(`Basic Client Details`.`Introducer Number`) as `basic intro number`,
first(IDSCli.`Introducer Name`) as `basic intro name`,
first(`Introducer_Accounts`.`Introducer Record Number`) as `introducer_Acc_intro number`,
first(IDSAcc.`Introducer Name`) as `introducer_Acc_intro name`,
first(`Basic Client Details`.`Insured Name`)
FROM
`Policy Details`,
`Introducer_Accounts`,
`Introducer Details-Scheme` AS IDSAcc,
`Basic Client Details`,
`Introducer Details-Scheme` AS IDSCli
WHERE
`Policy Details`.`Policy Record Number` = `Introducer_Accounts`.`Policy Record Number`
AND `Introducer_Accounts`.`Introducer Record Number` = IDSAcc.`Introducer Number`)
AND `Policy Details`.`Client Counter` = `Basic Client Details`.`Client Counter`
AND `Basic Client Details`.`Introducer Number` = IDSCli.`Introducer Number`)
AND `Basic Client Details`.`Introducer Number`<>`Introducer_Accounts`.`Introducer Record Number`
GROUP BY `Policy Details`.`Policy Record Number`

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLScholar]

Thanks mate! I had to remove the )`s in the

WHERE
`Policy Details`.`Policy Record Number` = `Introducer_Accounts`.`Policy Record Number`
AND `Introducer_Accounts`.`Introducer Record Number` = IDSAcc.`Introducer Number`)
AND `Policy Details`.`Client Counter` = `Basic Client Details`.`Client Counter`
AND `Basic Client Details`.`Introducer Number` = IDSCli.`Introducer Number`)
AND `Basic Client Details`.`Introducer Number`<>`Introducer_Accounts`.`Introducer Record Number`
GROUP BY `Policy Details`.`Policy Record Number`

bit but apart from that STAR

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[SQLScholar]

Still have the STAR, but i have a problem (maybe cause i had to remove the )`s.

But i am not getting all the rows i need.... about 2000 short.

Where should the opposite (`s be?

Thanks again

D

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[PHV]

Sorry for the typos. No paren needed at all.
What are the missing rows ?
I assumed you wanted only inner joins.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244