Joins - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Joins

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-12-14 : 05:37:14

I have the following View. My problem is that brfeesdet can have more than one entry. Consequently, I get the record duplicated for the number of entries on this table.I only want to get one field from it rctnum. Is there a way around this ?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-14 : 05:38:00

Not without seeing the view.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-12-14 : 05:45:46

Ooops - sorry

SELECT TOP 100 PERCENT dbo.brheader.apnkey, dbo.brheader.apnid, dbo.brheader.devdesc, dbo.brheader.appnamsurcom, dbo.brheader.compadd,
dbo.brdetails.worktype, dbo.brfeesdet.rctnum
FROM dbo.brheader INNER JOIN
dbo.brdetails ON dbo.brheader.apnkey = dbo.brdetails.apnkey INNER JOIN
dbo.paproposal ON dbo.brheader.apnkey = dbo.paproposal.apnkey INNER JOIN
dbo.brfeesdet ON dbo.brheader.apnkey = dbo.brfeesdet.apnkey
ORDER BY dbo.brheader.apnkey DESC, dbo.brheader.apnid

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-14 : 06:27:59

Do you always know which brfeesdet record you want each time?


SELECT TOP 100 PERCENT 
	dbo.brheader.apnkey, 
	dbo.brheader.apnid, 
	dbo.brheader.devdesc, 
	dbo.brheader.appnamsurcom, 
	dbo.brheader.compadd, 
	dbo.brdetails.worktype, 
	brfeesdet.rctnum
FROM dbo.brheader 
INNER JOIN dbo.brdetails 
	ON dbo.brheader.apnkey = dbo.brdetails.apnkey 
INNER JOIN dbo.paproposal 
	ON dbo.brheader.apnkey = dbo.paproposal.apnkey 
INNER JOIN (select Top 1 apnkey, rctnum from dbo.brfeesdet) brfeesdet
	ON dbo.brheader.apnkey = dbo.brfeesdet.apnkey
ORDER BY 
	dbo.brheader.apnkey DESC, 
	dbo.brheader.apnid

But you need to qualify the top 1 else you will get a random record each time.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-12-14 : 06:37:52

No. The view will eventually pick up a date parameter from a windows form which could list any number of records. dbo.brplot.datetimeofocc = Mydateparam. I have a second view based on this view.

SELECT dbo.PCCBuildingRegs1.apnid, dbo.PCCBuildingRegs1.devdesc, dbo.brplot.plotdesc, dbo.PCCBuildingRegs1.appnamsurcom,
dbo.PCCBuildingRegs1.compadd, dbo.PCCBuildingRegs1.worktype, dbo.brfeeitems.inspfee, dbo.brfeeitems.inspfeevat, dbo.brfeeitems.insptot,
dbo.brplot.datetimeofocc, dbo.PCCBuildingRegs1.rctnum
FROM dbo.PCCBuildingRegs1 INNER JOIN
dbo.brplot ON dbo.PCCBuildingRegs1.apnkey = dbo.brplot.apnkey LEFT OUTER JOIN
dbo.brfeeitems ON dbo.brplot.plotkey = dbo.brfeeitems.plotkey
WHERE (dbo.brfeeitems.insptot > 0)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-14 : 06:45:23

Hmmm, as they are not classed as duplicate records, then I am not sure there is much you can do at the view level. The only other way I can think of to do this is to either whittle the records down in your front end app or remove brfeesdet from this view and join it in later.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-12-14 : 06:48:54

How can I 'join it in later' ? What I need is the old Access DLookUp function.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources