I have a tricky SQL query I could use some help with.
I have a phone directory table, that was not designed very well. It has name, phone number, job description and primary_job_indicator. However, the primary_job_indicator isn't doing it's job. Not everyone has a primary_job.
Here's some sample data:
fname lname phone email job primary_job_ind Tim Burton 222-2222 [email protected] manager Y Jim Classy 222-3333 [email protected] instructor Y Jim Classy 222-3333 [email protected] dept head N Jane Dill 222-4444 [email protected] janitor N
I would like to select only the following, one row, with one job per person :
Tim Burton 222-2222 [email protected] manager Jim classy 222-3333 [email protected] instructor Jane Dill 222-4444 [email protected] janitor
I want to select from the table and avoid duplicate name+phone number+email.
If the person has only one row in the table, I want to select that row.
If the person has more than one row in the table, I want to select only one row - the one with primary_job_ind = 'Y' if it exists
I can't figure out how to do it :
SELECT fname, lname, phone, email, job FROM phonedirectory WHERE (( primary_job_ind = 'Y' ) OR ??????? )