Not Inner Join - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Not Inner Join

Author

Topic

sqlfan2

26 Posts

Posted - 2007-06-21 : 10:52:11

I have the following simple statement which does an inner join:

select
ProductAuth.StartDate, ProductAuth.EndDate,
ProductAuth.PRODUCT_ID, Products.Product
from ProductAuth inner join Products
on ProductAuth.PRODUCT_ID = Products.PRODUCT_ID WHERE
ProductAuth.MEMBER_ID=@MemberID;

and returns the correct rows for the authorized members.

Now I want to do the opposite. I want to find out which products the members are not authorized for.

Can anybody help here?

In the bigger picture, just in case someone knows the answer, these are a select statements to manage a product that a member is authorized to use.

On a web form, there are 3 list boxes. The first list box has a list of members, the second has a list of products that they are not authorized to use and the third a list of products that they are authorized to use.

If you click on a different member then it repopulates the auth'd and not auth'd lists. Between the two auth'd list are buttons that allow you to add and remove products.

This seems to be a fairly obvious and common application but I have been unable to find any further information on this subject. Does anybody know of a pattern that matches this type of management structure?

----------------------------------
Standing on the shoulders of giants

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 10:54:13

1. change INNER JOIN to Products LEFT JOIN ProductAuth
2. add ProductAuth.PRODUCT_ID IS NULL to where clause

KH
[spoiler]Time is always against us[/spoiler]

sqlfan2

26 Posts

Posted - 2007-06-21 : 15:28:21

Thanks khtan - you lead me down the right path. The only other thing that I needed to change was the right hand table in the INNER JOIN and replace it with a select statement:
(select * from ProductAuth where ProductAuth.MEMBER_ID=@MemberID)
and obviously remove that where clause from the end of the expression.

Thanks again!

----------------------------------
Standing on the shoulders of giants

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-22 : 00:05:03

You can replace and give the alias name for the derived table.

--------------------------------------------------
S.Ahamed

sqlfan2

26 Posts

Posted - 2007-06-22 : 03:59:03

pbguy: I don't understand? Can you give me an example?

----------------------------------
Standing on the shoulders of giants

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-22 : 05:06:26

[code]
SELECT
a.StartDate
,a.EndDate
,a.PRODUCT_ID
,b.Product
FROM
ProductAuth a
INNER JOIN
Products b
ON a.PRODUCT_ID = b.PRODUCT_ID
WHERE
a.MEMBER_ID=@MemberID

/*This IS the way khatan sugested*/
SELECT
a.StartDate
,a.EndDate
,a.PRODUCT_ID
,b.Product
FROM
ProductAuth a
Left JOIN
Products b
ON a.PRODUCT_ID = b.PRODUCT_ID
WHERE
a.MEMBER_ID=@MemberID
AND b.ProductiD IS NULL

/*This IS Another way TO accomplish the same thing, which a lot of people preffer*/

SELECT
a.StartDate
,a.EndDate
,a.PRODUCT_ID
,b.Product
FROM
ProductAuth a
WHERE
NOT EXISTS (
SELECT *
FROM
Products aa
WHERE aa.MemberID = @MemberID
)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources