Avatar of gnoled1
gnoled1Flag for United States of America asked on

SQL Command - CR XI

I have an SQL Command which joins multiple tables and finds the effective recruiter using an embedded select statement.  I would like to look up that recruiter code in another table to get their actual name (last and first).  Would this be done as part of the SQL Command or directly through the report writer?  (See SQL Command attached)

The actual name resides in the tbl_employee table where the recrutier_code is tbl_employee.code.
select collection_Code, 
           collection_date, 
           gl.group_location_id, 
           cgr.projected_donors, 
           cgr.scheduled_donors,
	    recruiter_code = (select Top (1) recruiter_code from group_location_recruiter 
						where group_location_id = gl.group_location_id and 
                                                effective_date <= cg.collection_date order by effective_date DESC),
           cv.vehicle_code
	from collection_General cg Join collection_groups cgr 
	on cg.collection_id = cgr.collection_id
        Join collection_vehicles cv on cg.collection_id = cv.collection_id
	Join group_location gl on gl.group_location_id = cgr.group_location_id
Order By collection_code

Open in new window

Crystal Reports

Avatar of undefined
Last Comment
gnoled1

8/22/2022 - Mon
KMusatoff

this would be a very dirty way...
firstName = (select first_name from tbl_employee where code = (select Top (1) recruiter_code from group_location_recruiter where group_location_id = gl.group_location_id and  effective_date <= cg.collection_date order by effective_date DESC))

Open in new window

ASKER
gnoled1

I thought of this but feel like there must be a better way.  Maybe some way to look up the code once the results are in CR?
ASKER CERTIFIED SOLUTION
Sharath S

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
gnoled1

Thank you.  I knew there had to be a better way.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy