November 20, 2007 at 11:14 am
I was provided a list of first names and last name. I need to upload this list into our system (into a table) and compare these names to our people table and return address info ect. I have created my table, uploaded the names, and now am trying to return results, but keep getting nothing. This is what i have so far, what am i missing?
select distinct p.first_name
,p.last_name
,ad.address_line_1
,ad.address_line_2
,ad.address_line_3
,ad.address_type
,ad.city
,ad.state
,ad.zip_code
,ad.day_phone
,ad.email_address
From people as p
inner join addressschedule as ad
on p.people_code_id=ad.people_org_code_id
inner join academic as a
on a.people_code_id=ad.people_org_code_id
inner join peopletype as pt
on a.people_code_id=pt.people_code_id
where ad.status='a'
and ad.address_type=p.preferred_add
and exists (select *
from Upload1
where upload1.first_name=p.first_name
and upload1.last_name=p.last_name)
November 20, 2007 at 11:34 am
Original post pretty sparse but here's a shot.
Start simple and work up, connect your people table to your upload table.
select *
FROM people p
RIGHT join upload1 u1 ON p.lastName = u1.lastName
and p.firstName = u1.firstname
where p.lastname is null
-- that finds items that were uploaded that do not have correct fname/lname.
Use that to start tracking the [WHY]
The names uploaded incorrectly?
The first names are abbreviated?
etc etc
November 20, 2007 at 12:48 pm
Thanks Daryl. That is what i did. I noticed that even just joining the first name field i got NO results. After further investigation, i noticed for some reason their was a space in the first name field on the file i uploaded, hence my null results. I removed the spaces and reuploaded. After that it worked!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply