August 30, 2013 at 1:21 pm
Hello all,
i have view which displays duplicate id numbers. Is there somehow i can alter my view to not pull up duplicate id's? Any help would be greatly appreciated.
Thanks:-)
SELECT DISTINCT
CAST(NAME_MASTER.ID_NUM AS VARCHAR) AS 'ID_NUM',
NAME_MASTER.PREFERRED_NAME,
NAME_MASTER.FIRST_NAME,
NAME_MASTER.LAST_NAME,
NAME_MASTER.MIDDLE_NAME,
IND_POS_HIST.POS_TITLE 'POSITION_TITLE',
IND_POS_HIST.POS_START_DTE,
EMPL_MAST.TERMINATION_DTE,
CAST(IND_POS_HIST.SUPER_ID_NUM AS VARCHAR) AS 'SUPERVISOR_ID',
EMPL_MAST.UDEF_5A_1 AS 'DEPT_CODE',
BIOGRAPH_MASTER.BIRTH_DTE,
ADDRESS_MASTER.ADDR_LINE_1 AS 'EMAIL',
mse_cardsystem_swipevalue.swipe_value
FROM NAME_MASTER INNER JOIN
IND_POS_HIST ON NAME_MASTER.ID_NUM = IND_POS_HIST.ID_NUM INNER JOIN
EMPL_MAST ON NAME_MASTER.ID_NUM = EMPL_MAST.ID_NUM AND IND_POS_HIST.ID_NUM = EMPL_MAST.ID_NUM INNER JOIN
BIOGRAPH_MASTER ON NAME_MASTER.ID_NUM = BIOGRAPH_MASTER.ID_NUM INNER JOIN
ADDRESS_MASTER ON NAME_MASTER.ID_NUM = ADDRESS_MASTER.ID_NUM INNER JOIN
mse_cardsystem_swipevalue ON NAME_MASTER.ID_NUM = mse_cardsystem_swipevalue.id_num
WHERE ADDRESS_MASTER.ADDR_CDE = '*EML' AND
IND_POS_HIST.POS_STS = 'P' AND
EMPL_MAST.ACT_INACT_STS = 'A'
AND IND_POS_HIST.POS_TITLE != 'Title'
August 30, 2013 at 1:51 pm
elee1969 (8/30/2013)
Hello all,i have view which displays duplicate id numbers. Is there somehow i can alter my view to not pull up duplicate id's? Any help would be greatly appreciated.
Thanks:-)
SELECT DISTINCTCAST(NAME_MASTER.ID_NUM AS VARCHAR) AS 'ID_NUM',
NAME_MASTER.PREFERRED_NAME,
NAME_MASTER.FIRST_NAME,
NAME_MASTER.LAST_NAME,
NAME_MASTER.MIDDLE_NAME,
IND_POS_HIST.POS_TITLE 'POSITION_TITLE',
IND_POS_HIST.POS_START_DTE,
EMPL_MAST.TERMINATION_DTE,
CAST(IND_POS_HIST.SUPER_ID_NUM AS VARCHAR) AS 'SUPERVISOR_ID',
EMPL_MAST.UDEF_5A_1 AS 'DEPT_CODE',
BIOGRAPH_MASTER.BIRTH_DTE,
ADDRESS_MASTER.ADDR_LINE_1 AS 'EMAIL',
mse_cardsystem_swipevalue.swipe_value
FROM NAME_MASTER INNER JOIN
IND_POS_HIST ON NAME_MASTER.ID_NUM = IND_POS_HIST.ID_NUM INNER JOIN
EMPL_MAST ON NAME_MASTER.ID_NUM = EMPL_MAST.ID_NUM AND IND_POS_HIST.ID_NUM = EMPL_MAST.ID_NUM INNER JOIN
BIOGRAPH_MASTER ON NAME_MASTER.ID_NUM = BIOGRAPH_MASTER.ID_NUM INNER JOIN
ADDRESS_MASTER ON NAME_MASTER.ID_NUM = ADDRESS_MASTER.ID_NUM INNER JOIN
mse_cardsystem_swipevalue ON NAME_MASTER.ID_NUM = mse_cardsystem_swipevalue.id_num
WHERE ADDRESS_MASTER.ADDR_CDE = '*EML' AND
IND_POS_HIST.POS_STS = 'P' AND
EMPL_MAST.ACT_INACT_STS = 'A'
AND IND_POS_HIST.POS_TITLE != 'Title'
Your view isn't returning duplicates, it is more likely that because of your join criteria you have more than one child table in the result set. If you are simply wanting to hide the values on multiple rows I would suggest doing that type of thing in the front end. Another option might be to add a ROW_NUMBER to your query and move it to a cte, then instead of selecting the ID you would use a case expression like:
case RowNum when 1 then ID else '' end as ID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 30, 2013 at 2:00 pm
The reason that you are getting duplicate IDs is that at least one of your tables has more than one row with a given ID_NUM and some of the other data in those several rows is different, because if it weren't the DISTINCT keyword would mean that these rows couldn't cause multiple rows in the view.
So, either you are getting several valid results for each id, and you have to decide somehow which of these rows you actually want and modify the query accordingly, or at least one of your tables contains invalid data and that's what you need to fix; of course both may be true.
Tom
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply