If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

There is a one to many relationship from Table1 to Table2 as well as a one to many relationship from Table2 to Table3.

I have a proc right now that searches for Table1 records with fields from Table1 and Table3. Table2 is in the join section only. The field from Table3 is not returned in the result set so I have duplicate records from Table1.

The only way I know of to prevent the duplicates is to use a select distinct but I've been told this isn't a good idea...suggested that I use a [cross][outer]apply. Any idea on how I can do this?

The only way I know of to prevent the duplicates is to use a select distinct but I've been told this isn't a good idea.

I will kind of support the guys about use of DISTINCT. As they say this is wrong, but with a little refinement this would amount to best practice.

A better way to phrase it would be that you should exhaust every other method of removing distinct rows before resorting to using DISTINCT. You should also know exactly why you have duplicate rows and be able to justify why they are appearing. DISTINCT is often used by (with the greatest of respect) inexperienced developers to "make the results right". The reason this is not preferred is that DISTINCT puts (an often unnecessary) load on query.

I would absolutely support n_i's method here. I always put any existence checks in a an EXISTS clause and never reference the table in the FROM clause. To me it is more logically sound, consistent and it also allows the optimiser to come up with more efficient plans.

This has been great, but I don't always have time for this during the workday. I was wondering if you guys would be coming to a city near me and if you have a late show.

Now, back on topic. Distinct can be an ugly word, because it always causes a sort. A group by, I know in DB2 and probably the others, can sometimes avoid a sort IF proper indexing is in place to support it.
Though as was already mentioned you should try to remove duplicates via your query where possible. In a different design of the first set of tables you mentioned, such as:
table1
table_id1(pk)
...

table3
table_id1
table_id2
table_id3
....
pk = table_id1
table_id2
table_id3
fk = table_id1
table_id2
You could do the check by looking for just the min/max of the next table in line or an open ended date for the current row of data, things of that sort. Also, with this sort of design you could just leave out table2 in the first query you mentioned.

Also, I think you and Rudy got off on wrong foot. He does supply quite a bit of help to folks out here and was just trying to point out with the contrived stuff, you had incorrect info and that does take everyones time. You could supply the true tables but truncated to what you need to get point across, or rename the columns before posting