Multiple records in a view, Only want 1 match

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple records in a view, Only want 1 match

Author

Topic

jlandwehr
Starting Member

23 Posts

Posted - 2009-02-18 : 11:16:25

I am trying to get the first record in a view to show up and cannot figure out where to place the constraint. I've tried Select DISTINCT and various combinations of LEFT OUTER, RIGHT OUTER, etc, but I keep getting 2 records (on a select where BMPNumber = 25) when I only want 1. The view is below:

CREATE VIEW GISLOADER.CTY_BMP_NEW_VIEW AS
SELECT GISLOADER.CTY_BMP.Shape, GISLOADER.CTY_BMP.OBJECTID, GISLOADER.CTY_BMP.BMPNumber,
SW.BMPCertificationDate, SW.BMPCertificationPEName, SW.BMPCertificationCompany, SW.BMPDrainageArea, SW.BMPMaintainedBy, SW.BMPDescription, SW.MunicipalityDesc,
SW.BMPTownshipID, SW.BMPSection, SW.BMPQuarterSection, SW.Watershed, SW.SubWatershed, SW. BMPFacilityTypeDesc, SW.ProjectName,
SW.LatestBMPInspectionDate,SW.BMPInserviceDate,SW.Project_PKID,ProjectDocuments.DocumentNumber
FROM GISLOADER.CTY_BMP RIGHT OUTER JOIN DEVDB1.PLU_STORMWATER_DEV.dbo.vw_GIS_Stormwater2 AS SW ON GISLOADER.CTY_BMP.BMPNumber = SW.BMPNumber
RIGHT OUTER JOIN DEVDB1.PLU_STORMWATER_DEV.dbo.ProjectDocuments AS ProjectDocuments ON ProjectDocuments.FK_Project = SW.Project_PKID

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-18 : 11:20:31

use top key word

jlandwehr
Starting Member

23 Posts

Posted - 2009-02-18 : 11:29:55

Thanks raky, but when I put select top 1 in the statement, I now get no records.

CREATE VIEW GISLOADER.CTY_BMP_NEW_VIEW AS
SELECT TOP 1 GISLOADER.CTY_BMP.Shape, GISLOADER.CTY_BMP.OBJECTID, GISLOADER.CTY_BMP.BMPNumber,
SW.BMPCertificationDate, SW.BMPCertificationPEName, SW.BMPCertificationCompany, SW.BMPDrainageArea, SW.BMPMaintainedBy, SW.BMPDescription, SW.MunicipalityDesc,
SW.BMPTownshipID, SW.BMPSection, SW.BMPQuarterSection, SW.Watershed, SW.SubWatershed, SW. BMPFacilityTypeDesc, SW.ProjectName,
SW.LatestBMPInspectionDate,SW.BMPInserviceDate,SW.Project_PKID,ProjectDocuments.DocumentNumber
FROM GISLOADER.CTY_BMP JOIN DEVDB1.PLU_STORMWATER_DEV.dbo.vw_GIS_Stormwater2 AS SW ON GISLOADER.CTY_BMP.BMPNumber = SW.BMPNumber
RIGHT OUTER JOIN DEVDB1.PLU_STORMWATER_DEV.dbo.ProjectDocuments AS ProjectDocuments ON ProjectDocuments.FK_Project = SW.Project_PKID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 11:57:01

quote:
Originally posted by jlandwehr

Thanks raky, but when I put select top 1 in the statement, I now get no records.

CREATE VIEW GISLOADER.CTY_BMP_NEW_VIEW AS
SELECT TOP 1 GISLOADER.CTY_BMP.Shape, GISLOADER.CTY_BMP.OBJECTID, GISLOADER.CTY_BMP.BMPNumber,
SW.BMPCertificationDate, SW.BMPCertificationPEName, SW.BMPCertificationCompany, SW.BMPDrainageArea, SW.BMPMaintainedBy, SW.BMPDescription, SW.MunicipalityDesc,
SW.BMPTownshipID, SW.BMPSection, SW.BMPQuarterSection, SW.Watershed, SW.SubWatershed, SW. BMPFacilityTypeDesc, SW.ProjectName,
SW.LatestBMPInspectionDate,SW.BMPInserviceDate,SW.Project_PKID,ProjectDocuments.DocumentNumber
FROM GISLOADER.CTY_BMP JOIN DEVDB1.PLU_STORMWATER_DEV.dbo.vw_GIS_Stormwater2 AS SW ON GISLOADER.CTY_BMP.BMPNumber = SW.BMPNumber
RIGHT OUTER JOIN DEVDB1.PLU_STORMWATER_DEV.dbo.ProjectDocuments AS ProjectDocuments ON ProjectDocuments.FK_Project = SW.Project_PKID

dont use top 1 inside view. use it in select statement where you select from the view

select top 1 * from GISLOADER.CTY_BMP_NEW_VIEW order by...

jlandwehr
Starting Member

23 Posts

Posted - 2009-02-18 : 12:11:49

Visakh16, It's hard to explain, but the view I'm referencing is actually a "spatial view" that is showing as a point on a map, (hence the shape column) but in this case there are two points in the same location showing up in the map. I only want one. So the problem lies in the fact that the view is grabbing both records, which the Select grabs too. I need to limit the one record from showing up in the view. Make sense?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 12:14:43

yup...but for that top 1 doesn't serve the purpose, it will just give you a single point alone from view. what you need to do is to use pk (or a unique valued column) and by means of it avoid selecting duplicates. if you can post some sample data and output you want out of them, i can help you more.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources