Create View with Mutliple Tables in SQL Server 2008
I'm converting an app to use SQL Server 2008 that is currently using SQLite. How would I do the following view in SQL Server 2008? I can't seem to figure out the syntax for calling multiple tables:
CREATE VIEW new_mimetypes AS
SELECT
DISTINCT fd.mimetype AS 'newMimetype'
FROM
files_detail AS fd
WHERE
NOT EXISTS (
开发者_开发百科 SELECT
m.mimetype
FROM
mimetypes AS m
WHERE
fd.mimetype = m.mimetype
)
[EDIT]
Nevermind. SQL Server Management Studio was complaining about syntax errors but it still took the SQL. That's what I get for thinking the IDE new what would work!
That syntax looks correct, are you getting an error?
I agree with @Adam Ruth that the syntax looks correct. I also wanted to add that you could use the "EXCEPT" operator as well to achieve the desired result:
CREATE VIEW [dbo].[new_mimetypes]
AS
SELECT mimetype As 'newMimetype' FROM files_detail
EXCEPT
SELECT mimetype FROM mimetypes
精彩评论