×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL Max with Fields from 2 Other Tables

SQL Max with Fields from 2 Other Tables

SQL Max with Fields from 2 Other Tables

(OP)
I am using SQL 2008R2 and need some assistance.

This query gives me the result I need except the CLIENT_CODE (in the C table) that corresponds with the LASTEST_TRANS:

SELECT
max(T.TRANS_ENTRY_DATE)AS LATEST_TRANS,
P.PAYEE_CODE,
P. PAYEE_NAME
FROM
{PREMIUMS_TRANSACTION] TRANS
INNER JOIN {PAYEES] P ON T.PAYEE_CODE = P.PAYEES_ID
INNER JOIN [CLIENTS] C ON T.BILLTO_ID = C.CLIENTS_ID
WHERE
NOT(T.TRANS_ENTRY_DATE) IS NULL AND
(P.DATE_OFF IS NULL)
GROUP BY
P. PAYEE_NAME,P.PAYEE_CODE

Desired results:
LATEST_TRANS PAYEE PAYEE_NAME CLIENT_CODE
5/14/2020 ABC ABC Ins CLIENTA
6/18/2018 AAC Safety CLIENTB
7/23/2019 AAP General CLIENTF
5/15/2020 ARC Arch CLIENTA

I've been searching and it seems I need a subquery, but I can't seem to translate their query to mine. Any help you could provide would be appreciated!




RE: SQL Max with Fields from 2 Other Tables

So, the same CLIENT_CODE may have one or more PAYEE and/or PAYEE_NAME:

LATEST_TRANS PAYEE PAYEE_NAME CLIENT_CODE
5/14/2020    ABC    ABC Ins    CLIENTA
6/18/2018    AAC    Safety     CLIENTB
7/23/2019    AAP    General    CLIENTF
5/15/2020    ARC    Arch       CLIENTA
 

How about:

SELECT MAX(TRANS_ENTRY_DATE) AS LATEST_TRANS,
PAYEE_CODE AS PAYEE,
PAYEE_NAME,
CLIENT_CODE
FROM (

SELECT
T.TRANS_ENTRY_DATE,
P.PAYEE_CODE,
P.PAYEE_NAME,
C.CLIENT_CODE
FROM
{PREMIUMS_TRANSACTION] TRANS
INNER JOIN {PAYEES] P ON T.PAYEE_CODE = P.PAYEES_ID
INNER JOIN [CLIENTS] C ON T.BILLTO_ID = C.CLIENTS_ID
WHERE
NOT(T.TRANS_ENTRY_DATE) IS NULL
AND(P.DATE_OFF IS NULL))
GROUP BY PAYEE, PAYEE_NAME, CLIENT_CODE


---- Andy

There is a great need for a sarcasm font.

RE: SQL Max with Fields from 2 Other Tables

(OP)
Thanks for your speedy reply Andy

I corrected my error:
FROM {PREMIUMS_TRANSACTION] TRANS to T and removed the where clause but I am still getting
Incorrect syntax near 'CLIENT_CODE' the last line.

What am I missing?


RE: SQL Max with Fields from 2 Other Tables

Post your current SQL


---- Andy

There is a great need for a sarcasm font.

RE: SQL Max with Fields from 2 Other Tables

(OP)
The query that works:

SELECT
max(T.TRANS_ENTRY_DATE)AS LATEST_TRANS,
T.PAYEE_CODE,
P.PAYEE_NAME
FROM
[xx].[xx].[dbo].[SQL_PREMIUMS_TRANSACTION_CODE] T
INNER JOIN [xx].[xx].[dbo].[SQL_PAYEES] P ON T.PAYEE_CODE = P.PAYEES_ID
INNER JOIN [xx].[xx].[dbo].[SQL_CLIENTS] C ON T.BILLTO_ID = C.CLIENTS_ID
WHERE
NOT(T.TRANS_ENTRY_DATE) IS NULL AND
(P.DATE_OFF IS NULL)
GROUP BY
P.PAYEE_NAME,T.PAYEE_CODE

The modified query that doesn't work:

SELECT MAX(TRANS_ENTRY_DATE) AS LATEST_TRANS,
PAYEE_CODE AS PAYEE,
PAYEE_NAME,
CLIENT_CODE
FROM (
SELECT
T.TRANS_ENTRY_DATE,
P.PAYEE_CODE,
P.PAYEE_NAME,
C.CLIENT_CODE
FROM
[xx].[xx].[dbo].[SQL_PREMIUMS_TRANSACTION_CODE] T
INNER JOIN [xx].[xx].[dbo].[SQL_PAYEES] P
ON T.PAYEE_CODE = P.PAYEES_ID
INNER JOIN [xx].[xx].[dbo].[SQL_CLIENTS] C
ON T.BILLTO_ID = C.CLIENTS_ID
WHERE
NOT(T.TRANS_ENTRY_DATE) IS NULL
AND(P.DATE_OFF IS NULL))
GROUP BY PAYEE, PAYEE_NAME, CLIENT_CODE

RE: SQL Max with Fields from 2 Other Tables

What do you get when you run this:

SELECT
T.TRANS_ENTRY_DATE,
P.PAYEE_CODE,
P.PAYEE_NAME,
C.CLIENT_CODE
FROM
[xx].[xx].[dbo].[SQL_PREMIUMS_TRANSACTION_CODE] T
INNER JOIN [xx].[xx].[dbo].[SQL_PAYEES] P
ON T.PAYEE_CODE = P.PAYEES_ID
INNER JOIN [xx].[xx].[dbo].[SQL_CLIENTS] C
ON T.BILLTO_ID = C.CLIENTS_ID
WHERE
NOT (T.TRANS_ENTRY_DATE) IS NULL
AND (P.DATE_OFF IS NULL)

I know you will not get the MAX date (yet...)


---- Andy

There is a great need for a sarcasm font.

RE: SQL Max with Fields from 2 Other Tables

(OP)
P.PAYEE_CODE can't be used but when I use either P.PAYEES_ID or T.PAYEE_CODE it returns the correct columns but 1M+ rows and I am expecting 610 rows.

Thanks

RE: SQL Max with Fields from 2 Other Tables


The error you're getting is probably because you need to alias the derived table.

CODE

SELECT MAX(TRANS_ENTRY_DATE) AS LATEST_TRANS,
PAYEE_CODE AS PAYEE,
PAYEE_NAME,
CLIENT_CODE
FROM (
SELECT T.TRANS_ENTRY_DATE, P.PAYEE_CODE, P.PAYEE_NAME, C.CLIENT_CODE FROM [xx].[xx].[dbo].[SQL_PREMIUMS_TRANSACTION_CODE] T INNER JOIN [xx].[xx].[dbo].[SQL_PAYEES] P ON T.PAYEE_CODE = P.PAYEES_ID INNER JOIN [xx].[xx].[dbo].[SQL_CLIENTS] C ON T.BILLTO_ID = C.CLIENTS_ID WHERE NOT(T.TRANS_ENTRY_DATE) IS NULL AND(P.DATE_OFF IS NULL)
) as alias GROUP BY PAYEE, PAYEE_NAME, CLIENT_CODE


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

RE: SQL Max with Fields from 2 Other Tables

(OP)
I tried the above but substituted T.PAYEE CODE - error "Invalid column name 'PAYEE'"

Then I removed the AS PAYEE:

SELECT MAX(TRANS_ENTRY_DATE) AS LATEST_TRANS,
PAYEE_CODE,
PAYEE_NAME,
CLIENT_CODE
FROM (
SELECT
T.TRANS_ENTRY_DATE,
T.PAYEE_CODE,
P.PAYEE_NAME,
C.CLIENT_CODE
FROM
[xx].[xx].[dbo].[SQL_PREMIUMS_TRANSACTION_CODE] T
INNER JOIN [xx].[xx].[dbo].[SQL_PAYEES] P
ON T.PAYEE_CODE = P.PAYEES_ID
INNER JOIN [xx].[xx].[dbo].[SQL_CLIENTS] C
ON T.BILLTO_ID = C.CLIENTS_ID
WHERE
NOT(T.TRANS_ENTRY_DATE) IS NULL
AND(P.DATE_OFF IS NULL)
) as alias
GROUP BY PAYEE_CODE, PAYEE_NAME, CLIENT_CODE

It ran but it still returned duplicates - 24,320 vs 610

This is really a puzzle for me. Thanks for all the help.

RE: SQL Max with Fields from 2 Other Tables

(OP)
Any ideas with my new info?? Thanks!

RE: SQL Max with Fields from 2 Other Tables

>The query that works:

SELECT
max(T.TRANS_ENTRY_DATE)AS LATEST_TRANS,
T.PAYEE_CODE,
P.PAYEE_NAME
FROM
[xx].[xx].[dbo].[SQL_PREMIUMS_TRANSACTION_CODE] T
INNER JOIN [xx].[xx].[dbo].[SQL_PAYEES] P ON T.PAYEE_CODE = P.PAYEES_ID
INNER JOIN [xx].[xx].[dbo].[SQL_CLIENTS] C ON T.BILLTO_ID = C.CLIENTS_ID
WHERE
NOT(T.TRANS_ENTRY_DATE) IS NULL
AND (P.DATE_OFF IS NULL)
GROUP BY P.PAYEE_NAME,T.PAYEE_CODE

If this is what works for you, and you want to add CLIENT_CODE field from SQL_CLIENTS table to your outcome, how does this new field relate to any table(s) already in use?

>it seems I need a subquery
You may be right...

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close