Create view to see if matching does not exist 1

[thep1]

I am trying to create a view that will look in 2 tables and if records are not in the other table then it will show those records.

SELECT dbo.AspNetUsers.Id, dbo.AspNetUsers.Email, dbo.AspNetUsers.UserName
FROM dbo.AspNetUsers INNER JOIN
dbo.AspNetUserRoles ON dbo.AspNetUsers.Id <> dbo.AspNetUserRoles.UserId

here is the query but when I execute it shows duplicates if the ID is not in dbo.AspNetUsers table. dont know if, what i am doing is correct or maybe somehting else needs to happen? hope to get some help.

 

[Andrzejek]

How about:

[pre]
SELECT dbo.AspNetUsers.Id, dbo.AspNetUsers.Email, dbo.AspNetUsers.UserName
FROM dbo.AspNetUsers
WHERE dbo.AspNetUsers.Id NOT IN (
SELECT dbo.AspNetUserRoles.UserId FROM dbo.AspNetUserRoles)
[/pre]


---- Andy

There is a great need for a sarcasm font.

 

[gmmastros]

This is textbook functionality for left join.

SELECT dbo.AspNetUsers.Id, 
       dbo.AspNetUsers.Email, 
       dbo.AspNetUsers.UserName
FROM   dbo.AspNetUsers 
       [!]LEFT[/!] JOIN dbo.AspNetUserRoles 
           ON dbo.AspNetUsers.Id [!]=[/!] dbo.AspNetUserRoles.UserId
Where  AspNetUserRoles.UserId Is NULL

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

Andy gave you the solution with IN or NOT IN. Perhaps easier to get than the solution that exists with the usual SET operations JOINS are.

Inverting the join condition doesn't lead to what you want. It's still a Join, not a non-join just because you replace = with <>. What you said was join all data with different ID, and that'll match almost all records with each other just not the ones with equal id. Mathematically, if you had nothing else but Ids that'd create all id combinations or permutations with different Ids on both sides.

If you want to check where NO record exists with the same ID, the join condition has to stay with equality, what changes is not getting matches, so no rows result with certain IDs.

Since you want to know exactly these IDs only existing on the left side, you'll need to change from INNER to LEFT join, you don't want an empty result if there is no match, you want a partial result and LEFT or RIGHT joins are for that, LEFT and RIGHT specify which part of a row you want, even if there is NO match. So that's the essential thing to look for here.

And to see exactly only those result rows, that had No match, you look for NULLs, which are put into the result for the RIGHT columns of the joined data.

And now that becomes a little unintuitive, you check for any of the right fields to be NULL:

SELECT dbo.AspNetUsers.Id, dbo.AspNetUsers.Email, dbo.AspNetUsers.UserName
FROM dbo.AspNetUsers [highlight #FCE94F]LEFT[/highlight] JOIN
dbo.AspNetUserRoles ON dbo.AspNetUsers.Id [highlight #FCE94F]=[/highlight] dbo.AspNetUserRoles.UserId
[highlight #FCE94F]WHERE dbo.AspNetUserRoles.Id IS NULL[/highlight]

"All rows of all tables have Ids, no Id value is null, that's not even allowed, as these fields are primary keys", is what you'll say, perhaps. You have to understand the WHERE clause checks the value of the result rows. If you'd SELECT * you'd get all data of AspNetUsers rows (that's what LEFT joins means) and when there is no match in dbo.AspNetUserRoles exists, all the columns of that right side are set NULL in the result. And that's the rows you're interested in. And that even works, even though you have an explicit field list of fields only from dbo.AspNetUsers, because virtually during the query still all columns of both joined tables are accessible and no matter if they are fully scanned (bad for performance) or matched by an index, you can imagine finding no match means a pointer into the joined table ends in nowhere land where everything is null and that can be seen from a WHERE clause, no matter if really just an index is looked into whch yields no match and only contains the UserID field and no others. No match means righttable.column IS NULL becomes true for any column of the right table. That's the "secret" of SQL Sets operations.

I like to use Id as it is a stronger indicator in the code I don't really mean to search for records with NULL Id value, but NULLs (no information) resulting from no match found.

I say all this not because LEFT JOINS Are superior to NOT IN existence check queries, but because this teaches an important concept of queries.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[thep1]

Thanks guys, I tried the one gmmastros suggested and got the result I was looking for.