join

  • Thread starter Thread starter Emma
  • Start date Start date
E

Emma

Hello,

I have a join which is working great. Unfortunately I would only like the
first record for Loans and Grants. Right now it brings up the first client
with the first loan and grant then the first client with the second loan and
grant etc... I would only like the client to appear once, thus the first loan
and grant only. Is this possible?

Here's the code:
SELECT [Tbl Client Information].*, [Tbl Loans].ID, [Tbl Grant].ID AS [ID_Tbl
Grant], [Tbl Loans].DateofLoanAgreementSigned, [Tbl
Grant].DateofGrantAgreementSigned FROM ([Tbl Client Information] LEFT JOIN
[Tbl Loans] ON [Tbl Client Information].[Client ID] = [Tbl Loans].[Client
ID]) LEFT JOIN [Tbl Grant] ON [Tbl Client Information].[Client ID] = [Tbl
Grant].[Client ID];

Thanks Emma
 
This is the direction I'm going in....

SELECT [Tbl Client Information].*
(SELECT TOP 1 [Tbl Loans].ID, [Tbl
Loans].DateofLoanAgreementSigned FROM [Tbl Loans])
(SELECT TOP 1 [Tbl Grant].ID AS [ID_Tbl Grant],
[Tbl Grant].DateofGrantAgreementSigned FROM [Tbl Grant])
FROM ([Tbl Client Information] LEFT JOIN [Tbl Loans] ON [Tbl Client
Information].[Client ID] = [Tbl Loans].[Client ID]) LEFT JOIN [Tbl Grant] ON
[Tbl Client Information].[Client ID] = [Tbl Grant].[Client ID];
 
You have to filter your query for anything that you don't want. While there
are many ways of doing this, this is usually done by using a subquery in
combination with the Not Exists() operator; something like (untested):

SELECT TCI.*, TL.Id, TG.Id AS [ID_Tbl
Grant], TL.DateofLoanAgreementSigned, TG.DateofGrantAgreementSigned

FROM ([Tbl Client Information] as TCI LEFT JOIN
[Tbl Loans] as TL on TCI.[Client ID] = TL.[Client ID]) LEFT JOIN [Tbl
Grant] as TG on TCI.[Client ID] = TG.[Client ID]

Where Not Exists (Select * from [Tbl Loans] as TL2 where TL2.[Client ID] =
TCI.[Client ID]
and Tl2.Id < TL.Id)

And Not Exists (Select * from [Tbl Grant] as TG2 where TG2.[Client ID] =
TCI.[Client ID]
and TG2.Id < TG.Id)

I must warn you that the use of space inside the name of a table or of a
column will give you a lot of trouble later; especially with subqueries as
you often have to use the operator [ ]. instead of the parenthesis for
subqueries and you cannot use the operator [] for the names with spaces
inside them inside the [ ]. operator for a subquery. Instead of [Tbl
Grant], you will greatly simplify your life if you were to simply use
TblGrant or even better, simply Grant. You should also use Grant.IdGrant
instead of Grant.Id. Using the same name Id for multiple tables will create
a lot of confusion. In the same way, you should use ClientInformationId
instead of ClientId for the table ClientInformation.

I'm also not sure if you really need to use a Left Join for either the table
Grant or the table Loan.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)
 
Hi Sylvain,

Thankyou for all your help. The query works great! I took your advice and
renamed the ID's to LoanID and GrantID. It works beautifully. So I was
wondering I see that you are a consultant for SQL - Server but in french. I'm
look to pay someone to be my mentor to converting my MS Access databse in SQL
Server 2008 Enterprise in English. Would you be willling to help me or do you
know someone that could?|

Thanks Again,
Emma

Sylvain Lafontaine said:
You have to filter your query for anything that you don't want. While there
are many ways of doing this, this is usually done by using a subquery in
combination with the Not Exists() operator; something like (untested):

SELECT TCI.*, TL.Id, TG.Id AS [ID_Tbl
Grant], TL.DateofLoanAgreementSigned, TG.DateofGrantAgreementSigned

FROM ([Tbl Client Information] as TCI LEFT JOIN
[Tbl Loans] as TL on TCI.[Client ID] = TL.[Client ID]) LEFT JOIN [Tbl
Grant] as TG on TCI.[Client ID] = TG.[Client ID]

Where Not Exists (Select * from [Tbl Loans] as TL2 where TL2.[Client ID] =
TCI.[Client ID]
and Tl2.Id < TL.Id)

And Not Exists (Select * from [Tbl Grant] as TG2 where TG2.[Client ID] =
TCI.[Client ID]
and TG2.Id < TG.Id)

I must warn you that the use of space inside the name of a table or of a
column will give you a lot of trouble later; especially with subqueries as
you often have to use the operator [ ]. instead of the parenthesis for
subqueries and you cannot use the operator [] for the names with spaces
inside them inside the [ ]. operator for a subquery. Instead of [Tbl
Grant], you will greatly simplify your life if you were to simply use
TblGrant or even better, simply Grant. You should also use Grant.IdGrant
instead of Grant.Id. Using the same name Id for multiple tables will create
a lot of confusion. In the same way, you should use ClientInformationId
instead of ClientId for the table ClientInformation.

I'm also not sure if you really need to use a Left Join for either the table
Grant or the table Loan.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


Emma said:
Hello,

I have a join which is working great. Unfortunately I would only like the
first record for Loans and Grants. Right now it brings up the first client
with the first loan and grant then the first client with the second loan
and
grant etc... I would only like the client to appear once, thus the first
loan
and grant only. Is this possible?

Here's the code:
SELECT [Tbl Client Information].*, [Tbl Loans].ID, [Tbl Grant].ID AS
[ID_Tbl
Grant], [Tbl Loans].DateofLoanAgreementSigned, [Tbl
Grant].DateofGrantAgreementSigned FROM ([Tbl Client Information] LEFT JOIN
[Tbl Loans] ON [Tbl Client Information].[Client ID] = [Tbl Loans].[Client
ID]) LEFT JOIN [Tbl Grant] ON [Tbl Client Information].[Client ID] = [Tbl
Grant].[Client ID];

Thanks Emma
Click to expand...


.
Click to expand...
 
Yes, of course, I can probably help you and there is also probably a lot of
other people who could probably help you.

However, as I don't know what you have at this moment, where you want to go
with that and how exactly you want to proceed (do you want to do much of the
work yourself; only asking for some precisions here and there or if you want
a more involved line of action?), it's hard for me to give you any hard
answer about that or to look around for someone else.

If you want to, you can contact me by email at the following address:
sylvain aei ca. Simply fill the blanks with the usual internet email
characters. You can also find other contact informations at the bottom of
the following page: http://english.sylvainlafontaine.com .

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


Emma said:
Hi Sylvain,

Thankyou for all your help. The query works great! I took your advice and
renamed the ID's to LoanID and GrantID. It works beautifully. So I was
wondering I see that you are a consultant for SQL - Server but in french.
I'm
look to pay someone to be my mentor to converting my MS Access databse in
SQL
Server 2008 Enterprise in English. Would you be willling to help me or do
you
know someone that could?|

Thanks Again,
Emma

Sylvain Lafontaine said:
You have to filter your query for anything that you don't want. While
there
are many ways of doing this, this is usually done by using a subquery in
combination with the Not Exists() operator; something like (untested):

SELECT TCI.*, TL.Id, TG.Id AS [ID_Tbl
Grant], TL.DateofLoanAgreementSigned, TG.DateofGrantAgreementSigned

FROM ([Tbl Client Information] as TCI LEFT JOIN
[Tbl Loans] as TL on TCI.[Client ID] = TL.[Client ID]) LEFT JOIN [Tbl
Grant] as TG on TCI.[Client ID] = TG.[Client ID]

Where Not Exists (Select * from [Tbl Loans] as TL2 where TL2.[Client ID]
=
TCI.[Client ID]
and Tl2.Id < TL.Id)

And Not Exists (Select * from [Tbl Grant] as TG2 where TG2.[Client ID] =
TCI.[Client ID]
and TG2.Id < TG.Id)

I must warn you that the use of space inside the name of a table or of a
column will give you a lot of trouble later; especially with subqueries
as
you often have to use the operator [ ]. instead of the parenthesis for
subqueries and you cannot use the operator [] for the names with spaces
inside them inside the [ ]. operator for a subquery. Instead of [Tbl
Grant], you will greatly simplify your life if you were to simply use
TblGrant or even better, simply Grant. You should also use Grant.IdGrant
instead of Grant.Id. Using the same name Id for multiple tables will
create
a lot of confusion. In the same way, you should use ClientInformationId
instead of ClientId for the table ClientInformation.

I'm also not sure if you really need to use a Left Join for either the
table
Grant or the table Loan.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


Emma said:
Hello,

I have a join which is working great. Unfortunately I would only like
the
first record for Loans and Grants. Right now it brings up the first
client
with the first loan and grant then the first client with the second
loan
and
grant etc... I would only like the client to appear once, thus the
first
loan
and grant only. Is this possible?

Here's the code:
SELECT [Tbl Client Information].*, [Tbl Loans].ID, [Tbl Grant].ID AS
[ID_Tbl
Grant], [Tbl Loans].DateofLoanAgreementSigned, [Tbl
Grant].DateofGrantAgreementSigned FROM ([Tbl Client Information] LEFT
JOIN
[Tbl Loans] ON [Tbl Client Information].[Client ID] = [Tbl
Loans].[Client
ID]) LEFT JOIN [Tbl Grant] ON [Tbl Client Information].[Client ID] =
[Tbl
Grant].[Client ID];

Thanks Emma
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top