Hi,
I have a sql view, which i need to retrieve information. Question: I need three different names and surnames from the users table. The one who accepted the quote, who opened the quote and last who the quote has been assigned to.
The Error that i get is this;
Server: Msg 170, Level 15, State 1, Line 25
Line 25: Incorrect syntax near 'QuoteID'.
I appreciate the help.
SELECT dbo.Quotes.QuoteName, dbo.QuoteStatus.Description AS Status, dbo.QuoteAccepted.AcceptDate, dbo.a.FirstName+' '+dbo.a.LastName as [Accepted By], dbo.o.FirstName+' '+dbo.o.LastName as [Opened By], dbo.QuoteNotes.Note, dbo.QuoteNotes.NoteDate, dbo.ato.FirstName+' '+dbo.ato.LastName as [Assigned To], dbo.QuoteFollowUPs.LastFollowup, dbo.QuoteFollowUPs.NextFollowUp, dbo.QuoteFollowUPs.Comment, dbo.QuoteFollowUPs.HoursSpent, dbo.QuoteFollowUPs.DaysSpent, dbo.QuoteBookingReferences.BookRef, dbo.QuoteTourTypes.Description AS [Tour Type], dbo.QuoteTypes.Description AS [Quote Type], dbo.Quotes.QuoteID, dbo.QuotesSent.DateSent, dbo.QuotesSent.Cost, dbo.QuotesSent.PaxFrom, dbo.QuotesSent.PaxTo, dbo.QuotesSent.Currency, dbo.QuotesSent.Selling, dbo.QuotesSent.MasterPaxAmountFROM dbo.QuoteBookingReferences INNER JOINdbo.Quotes INNER JOINdbo.QuoteStatus_Quotes ON dbo.Quotes.QuoteID = dbo.QuoteStatus_Quotes.QuoteID INNER JOINdbo.QuoteStatus ON dbo.QuoteStatus_Quotes.StatusID = dbo.QuoteStatus.StatusID ON dbo.QuoteBookingReferences.QuoteID = dbo.Quotes.QuoteID INNER JOINdbo.QuoteFollowUPs ON dbo.Quotes.QuoteID = dbo.QuoteFollowUPs.QuoteID INNER JOINdbo.QuoteNotes ON dbo.Quotes.QuoteID = dbo.QuoteNotes.QuoteID INNER JOINdbo.QuoteOpenedBy ON dbo.Quotes.QuoteID = dbo.QuoteOpenedBy.QuoteID INNER JOINdbo.Quotes_QuoteTourTypes ON dbo.Quotes.QuoteID = dbo.Quotes_QuoteTourTypes.QuoteID INNER JOINdbo.QuoteTourTypes ON dbo.Quotes_QuoteTourTypes.TypeofToursID = dbo.QuoteTourTypes.TypeofToursID INNER JOINdbo.Quotes_QuoteTypes ON dbo.Quotes.QuoteID = dbo.Quotes_QuoteTypes.QuoteID Inner JOINdbo.QuoteTypes ON dbo.Quotes_QuoteTypes.TypeofQuoteID = dbo.QuoteTypes.TypeofQuoteID INNER JOINdbo.QuotesSent ON dbo.Quotes.QuoteID = dbo.QuotesSent.QuoteID LEFT OUTER JOIN/*Trying to use the same table to get back different names and surnames*/dbo.Users INNER JOINdbo.Users a INNER JOINdbo.Users o INNER JOINdbo.Users ato INNER JOINdbo.QuoteAccepted ON dbo.a.UserID = dbo.QuoteAccepted.UserID INNER JOINdbo.QuoteAssignTo ON dbo.ato.UserID = dbo.QuoteAssignTo.UserID ON dbo.QuoteOpenedBy.UserID = dbo.o.UserID AND dbo.Quotes.QuoteID = dbo.QuoteAssignTo.QuoteID AND dbo.Quotes.QuoteID = dbo.QuoteAccepted.QuoteID
when you have aliases, you cannot prefix them with dbo. you also are better to put the ON clause next to the INNER JOIN
INNER JOIN dbo.Users a ON a.UserID = dbo.QuoteAccepted.UserID
INNER JOIN dbo.Users o ON dbo.QuoteOpenedBy.UserID = o.UserID
INNER JOIN dbo.Users ato ON ato.UserID = dbo.QuoteAssignTo.UserID
INNER JOIN dbo.QuoteAccepted ON dbo.Quotes.QuoteID = dbo.QuoteAccepted.QuoteID
0
TIO-SolutionsAuthor Commented:
Server: Msg 170, Level 15, State 1, Line 25
Line 25: Incorrect syntax near 'QuoteID'.
SELECT dbo.Quotes.QuoteName, dbo.QuoteStatus.Description AS Status, dbo.QuoteAccepted.AcceptDate, (SELECT a.FirstName+' '+a.LastName FROM Users a WHERE a.UserID = dbo.QuoteAccepted.UserID AND dbo.Quotes.QuoteID = dbo.QuoteAccepted.QuoteID)as [Accepted By], (SELECT o.FirstName+' '+o.LastName FROM Users o WHERE o.UserID = dbo.QuoteOpenedBy.UserID AND dbo.Quotes.QuoteID = dbo.QuoteOpenedBy.QuoteID) as [Opened By], dbo.QuoteNotes.Note, dbo.QuoteNotes.NoteDate, (SELECT a.FirstName+' '+a.LastName FROM Users a WHERE a.UserID = dbo.QuoteAssignTo.UserID AND Quotes.QuoteID = dbo.QuoteAssignTo.QuoteID) as [Assigned To], dbo.QuoteFollowUPs.LastFollowup, dbo.QuoteFollowUPs.NextFollowUp, dbo.QuoteFollowUPs.Comment, dbo.QuoteFollowUPs.HoursSpent, dbo.QuoteFollowUPs.DaysSpent, dbo.QuoteBookingReferences.BookRef, dbo.QuoteTourTypes.Description AS [Tour Type], dbo.QuoteTypes.Description AS [Quote Type], dbo.Quotes.QuoteID, dbo.QuotesSent.DateSent, dbo.QuotesSent.Cost, dbo.QuotesSent.PaxFrom, dbo.QuotesSent.PaxTo, dbo.QuotesSent.Currency, dbo.QuotesSent.Selling, dbo.QuotesSent.MasterPaxAmountFROM dbo.QuoteBookingReferences INNER JOINdbo.Quotes INNER JOIN dbo.QuoteStatus_Quotes ON dbo.Quotes.QuoteID = dbo.QuoteStatus_Quotes.QuoteIDINNER JOIN dbo.QuoteStatus ON dbo.QuoteStatus_Quotes.StatusID = dbo.QuoteStatus.StatusID ON dbo.QuoteBookingReferences.QuoteID = dbo.Quotes.QuoteID INNER JOIN dbo.QuoteFollowUPs ON dbo.Quotes.QuoteID = dbo.QuoteFollowUPs.QuoteID INNER JOIN dbo.QuoteNotes ON dbo.Quotes.QuoteID = dbo.QuoteNotes.QuoteID INNER JOIN dbo.Quotes_QuoteTourTypes ON dbo.Quotes.QuoteID = dbo.Quotes_QuoteTourTypes.QuoteID INNER JOIN dbo.QuoteTourTypes ON dbo.Quotes_QuoteTourTypes.TypeofToursID = dbo.QuoteTourTypes.TypeofToursID INNER JOIN dbo.Quotes_QuoteTypes ON dbo.Quotes.QuoteID = dbo.Quotes_QuoteTypes.QuoteID INNER JOIN dbo.QuoteTypes ON dbo.Quotes_QuoteTypes.TypeofQuoteID = dbo.QuoteTypes.TypeofQuoteID INNER JOIN dbo.QuotesSent ON dbo.Quotes.QuoteID = dbo.QuotesSent.QuoteID Left Outer JoinUsers Inner JOIN dbo.QuoteAccepted ON dbo.QuoteAccepted.UserID = dbo.Users.UserID AND dbo.Quotes.QuoteID = dbo.QuoteAccepted.QuoteID INNER JOIN dbo.QuoteOpenedBy ON dbo.QuoteOpenedBy.UserID = dbo.Users.UserID AND dbo.Quotes.QuoteID = dbo.QuoteOpenedBy.QuoteIDINNER JOIN dbo.QuoteAssignTo ON dbo.QuoteAssignTo.UserID = dbo.Users.UserID AND Quotes.QuoteID = dbo.QuoteAssignTo.QuoteID
At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.
I see right at the beginning that you don't have the ON clause again:
FROM dbo.Users INNER JOIN
dbo.QuoteBookingReferences
so comment out every JOINs and add them one by one (ensuring it is working before adding another one)