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
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