Returning duplicated values only once from a join query

Question

I'm trying to extract info from a table in my database based on a persons job. In one table i have all the clients info, in another table linked by ID_no their job title and the branches theyre associated with. the problem I'm having is when i join both tables I'm returning some duplicates because a person can be associated with more than one branch.

I would like to know how to return the duplicated values only once, because all I care about for the moment is the persons id number and what their job title is.

SELECT *
FROM dbo.employeeinfo AS ll
LEFT OUTER JOIN employeeJob AS lly
    ON ll.id_no = lly.id_no
WHERE lly.job_category = 'cle'

I know Select Distinct will not work in this situation since the duplicated values return different branches. Any help would be appreciated. Thanks

I'm using sql server 2008 by the way

*edit to show result i would like

------     ll.         ll.       lly.       lly.
rec_ID --employeeID---Name-----JobTitle---Branch------
1        JX100        John     cle        london      
2        JX100        John     cle        manchester  
3        JX690        Matt     89899      london      
4        JX760        Steve    12345      london      

I would like the second record to not display because i'm not interested in the branch. i just need to know the employee id and his job title, but because of how the tables are structured it's returning JX100 twice because he's recorded as working in 2 different branches

Answer 1

You must use SELECT DISTINCT and specify you ONLY want person id number and job title.

I don't know exactly your fields name, but I think something like this could work.

SELECT DISTINCT ll.id_no AS person_id_number,
    lly.job AS person_job
FROM dbo.employeeinfo AS ll LEFT OUTER JOIN 
    employeeJob AS lly ON ll.id_no = lly.id_no
WHERE lly.job_category = 'cle'