Web Wiz - Green Windows Web Hosting

  New Posts New Posts RSS Feed - Avoiding duplicate records
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Avoiding duplicate records

 Post Reply Post Reply
Author
fosterm View Drop Down
Newbie
Newbie
Avatar

Joined: 20 May 2003
Location: United Kingdom
Status: Offline
Points: 1
Post Options Post Options   Thanks (0) Thanks(0)   Quote fosterm Quote  Post ReplyReply Direct Link To This Post Topic: Avoiding duplicate records
    Posted: 20 May 2003 at 6:22am

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

 

Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.


Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.