0

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

germantom
  • 405
  • 2
  • 6
  • 15
  • could you show what is the result you want, on an example of 2 (or more records) with the same client and different jobs. Since the 2 rows will not be really duplicate. do you need to just show one job and ignore the rest? show all jobs in a single field? – Thanos Darkadakis Sep 26 '13 at 09:56
  • @ThanosDarkadakis Thanks for replying, I've added the results I want above – germantom Sep 26 '13 at 10:38

1 Answers1

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'
tezzo
  • 10,858
  • 1
  • 25
  • 48
  • thanks it looks like this worked! If I wanted to add more fields from ll. would I just include group by and then put all the fields I want displayed after? This seems to work for me but I'm just wondering if it's correct – germantom Sep 26 '13 at 12:06
  • 1
    You can include them in the list of field extracted without using a GROUP BY clause. – tezzo Sep 26 '13 at 12:15
  • I swaer I tried this earlier and it didn't work! Tried again just now without group by and it worked. Thanks AGAIN – germantom Sep 26 '13 at 12:26
  • could you answer another quick question please..? when I'm using the view I created above in a stored procedure I want to count the results that appear in the view. problem I'm having is it's counting all the duplicates. how would I fix this to only count the distinct? – germantom Sep 27 '13 at 13:58