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.
| Author |
Topic |
|
paulsmith
Starting Member
1 Post |
 Posted - 2007-08-28 : 22:24:00
|
| I have a view that is written as follows:SELECT TOP (100) PERCENT dbo.passenger.FirstName + ' ' + dbo.passenger.LastName AS PassengerName, COUNT(dbo.drive.Education) + COUNT(dbo.drive.RoundTrip) AS EducationRidesFROM dbo.drive INNER JOIN dbo.passenger ON dbo.drive.PassengerID = dbo.passenger.PassengerIDWHERE (CONVERT(char(6), dbo.drive.Date, 112) = CASE substring(CAST(CONVERT(char(6), getdate(), 112) - 1 AS char(6)), 5, 2) WHEN '00' THEN CONVERT(char(6), getdate(), 112) - 88 ELSE CONVERT(char(6), getdate(), 112) - 1 END) AND (dbo.drive.Education > 0)GROUP BY dbo.drive.PassengerID, dbo.passenger.FirstName, dbo.passenger.LastNameORDER BY PassengerNameI have another view that is written identically to the one above by instead of Education/EducationRides is it EmpVoc/EducationRides respectively. Then I have a couple more views with other types of rides...What I am trying to do is combine these views so PassengerName is in the first column and in the second column is Education and the next is EmpVoc, and so on. But the join statement is eluding me.I am not that proficient in MsSQL Joins but I am trying!I'd appreciate anyone's help.Thanks,Paul Smith |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-29 : 07:11:34
|
| SELECT TOP (100) PERCENT ...ORDER BY PassengerNameSorry, can't quite get my head around the problem in the time I have available, but "TOP (100) PERCENT" and "ORDER BY" are best avoided in VIEWs. Sounds like you are trying to do presentation stuff in the VIEWs that ought to be done in the SELECT from that View instead.Kristen |
 |
|
|
|
|
|
|
|
Join problems