data duplication

Dear All

I have SELECT clause which uses loads of OUTER JOINS to produce a table with loads of data needed for a front-end appliation

But, some of the data it produces is replicated because of the JOINS

e.g. an example of data is this

person_id LastName FirstName LanguageSpoken Speciality

1234 mySurNme my1stName FRENCH speciality1
1234 mySurNme my1stName GERMAN speciality2
1234 mySurNme my1stName FRENCH speciality1
1234 mySurNme my1stName GERMAN speciality2

I just want it to come out like this

person_id LastName FirstName LanguageSpoken Speciality

1234 mySurNme my1stName FRENCH speciality1
1234 mySurNme my1stName GERMAN speciality2

Can anyone help

GillYou can try "Select distinct ...". This will eliminate duplicate rows.|||Originally posted by ts555
You can try "Select distinct ...". This will eliminate duplicate rows.

That's the first thing I tried - that doen't work|||If you use distinct, it should not return duplicates. It would be helpful if you give us your query.|||Originally posted by jzhu
If you use distinct, it should not return duplicates. It would be helpful if you give us your query.

A couple of things come to mind.

1) Is the data clean? Dup records could cause a problem.

2) Take a close look at your joins, you could be receiving a cartesian product.

3) If the joins are correct the problem may be in the WHERE criteria. When joining many tables, especially where 1 to many relationships exists, its possible your WHERE clause is not specific enough to bring the number of returns down to a single row.sql

Customizing Report Model AutoGeneration

Is there any way to customize the expressions that are generated when using the AutoGenerate wizard to produce a model in Visual/BI Studio? There are certain expressions that my team always has to generate for nearly every datetime attribute. Adding the expressions manually is very tedious since the expression window does not allow simple copy & paste.

There is no supported way to customize the fields created by the model generation wizard. However, if you poke around a little on your file system (for instance, under C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ReportModelProjects), you might find an undocumented file that, when modified, seems to influence the output of the model generation wizard. Remember, though, you're on your own. :)

Customizing Report Model AutoGeneration

Is there any way to customize the expressions that are generated when using the AutoGenerate wizard to produce a model in Visual/BI Studio? There are certain expressions that my team always has to generate for nearly every datetime attribute. Adding the expressions manually is very tedious since the expression window does not allow simple copy & paste.

There is no supported way to customize the fields created by the model generation wizard. However, if you poke around a little on your file system (for instance, under C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ReportModelProjects), you might find an undocumented file that, when modified, seems to influence the output of the model generation wizard. Remember, though, you're on your own. :)