Stopping duplicate records

[fosterm]

Hi

I have a view that is based on the following tables

Provisions
Lst_cat
Def_Cat

A provision can have multiple catagories so the Lst_cat table contains all
the catagories assigned to a provision
The def_cat table contains the list of available catagories

My problem is that I need to return certain provisions based on a catagory
description. However I want to only return unique records

If I connect the provisions and lst_cat table then by using distinct only
unique records are returned.
SELECT DISTINCT dbo.Provisions.Prov_Id
FROM dbo.Provisions INNER JOIN
dbo.Lst_Cats ON dbo.Provisions.Prov_Id =
dbo.Lst_Cats.Lstcat_id


However, I need to connect the def_cat table as well to search on the name
of the cataogry
SELECT DISTINCT dbo.Provisions.Prov_Id, dbo.Def_Cat.DefCat_Name
FROM dbo.Provisions INNER JOIN
dbo.Lst_Cats ON dbo.Provisions.Prov_Id =
dbo.Lst_Cats.Lstcat_id INNER JOIN
dbo.Def_Cat ON dbo.Lst_Cats.Lstcat_catid =
dbo.Def_Cat.DefCat_id

This now returns hundreds of instances of the same record and distinct no
longer works

Any ideas ?

Many thanks

Mark

 

[SQLSister]

Well of course you get multiple records, you have multiple categories for each provision ID. Distinct works for the entire data set not just for one field.

Can't help you get only one ID and one category name unless you give some basis for choosing which of the multiple category Names you want returned.

Lets start with some sample data and you will see what I mean

ProvisionID CatID
1 2
1 4
2 1
2 3
2 2

CatID CatName
1 Apples
2 Oranges
3 Grapes
4 Watermelon

When you link then together in one query how does it know if you want to see ProvisionID of 1 and CatName of Oranges or ProvisionID 1 and CateName of Watermelon? It has to return both as distinct because it has not been provided a method of choosing whcich one you want returned.