CODEWITHSUNDEEP
sql-server-2008syntaxsqlite
dragonmantank
dragonmantank

Reputation: 15456

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!

Upvotes: 0

Views: 13072

Answers (2)

dugas
dugas

Reputation: 12443

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

Upvotes: 2

Adam Ruth
Adam Ruth

Reputation: 3655

That syntax looks correct, are you getting an error?

Upvotes: 4

Related Questions