asked on

Compare 2 tables to exclude records from one of them

I have 2 tables.

Table A: Users (members of my site).
Table B: Users who paid for membership at my site.

At Table B: there is a field called strPaid. If there is a value (1), then the users has paid for its membership.

There is a relation (one to many) from A to B:
Table A: - primary key is the pkIntUserID
Table B: - foreign key is the fkIntUserID

Here is what I want:

I want to users who have paid NOT to appear on a view

I realize that it's not gonna happen if I create a normal view using these 2 tables.

How should I do it?

Jared_S

Create View  MyView as
select * from tableA 
where not exists (select null from tableA, tableB where tableA.pkIntUserID = tableB.fkIntUserID and tableB.strPaid = '1');

Open in new window

or depending on your data in table B

Create View  MyView as
select distinct fkIntUserID from tableB where strPaid <> '1'

Open in new window

jensjakobsen

ASKER

Thank you very much for your quick response.

I used the first example - the second view only contains records if a user actually has paid for its membership.

I converted the first example like this:

select * from tbl01User
where not exists (select null from tbl01User, tbl055PaidMembers where tbl01User.pkIntUserID = tbl055PaidMembers.fkIntUserID and tbl055PaidMembers.strPaid = '1');

Open in new window

Once I executed it 2 things happened:

No records were returned (all members who were du to pay should have been visible)
The code was immediately re-written by Microsoft SQL Management Studion to:

SELECT     TOP (100) PERCENT pkIntUserID, strFName, strLName, strMail
FROM         dbo.tbl01User
WHERE     (NOT EXISTS
                          (SELECT     NULL AS EXPR
                            FROM          dbo.tbl01User AS tbl01User_1 INNER JOIN
                                                   dbo.tbl055PaidMembers ON tbl01User_1.pkIntUserID = dbo.tbl055PaidMembers.fkIntUserID
                            WHERE      (dbo.tbl055PaidMembers.strPaid = '1')))

Open in new window

Jared_S

The re-write is the same thing, just with a more verbose syntax.

Can you execute the where statement independently and see what you get?

Just replace the select null with select count(*)

My first thought is that strPaid = '1' may need changed to strPaid = 1

jensjakobsen

ASKER

Both suggestions gave the same result.

strPaid is the datatype of nchar.

Jared_S

Are the ID's we're joining on in both tables the same data type?

Maybe on is an integer and the other is not?

jensjakobsen

ASKER

They are both "numeric(18, 0)"

Jared_S

Is strPaid null if they haven't paid yet?

That would keep you from getting any results. This would fix it.

select * from tbl01User
where not exists (select null from tbl01User, tbl055PaidMembers where tbl01User.pkIntUserID = tbl055PaidMembers.fkIntUserID and isnull(tbl055PaidMembers.strPaid,0) = '1');

Open in new window

jensjakobsen

ASKER

Both statements gave me the result of "1".

I do have one member which I have marked as paid (strPaid = 1)

Jared_S

Check my last post again. It hit me right after I posted, so I edited my comment - apparently not fast enough though.

jensjakobsen

ASKER

OK - I copy/pasted the statement again. It leaves no result :(

If strPaid is NULL then it means that they haven't paid yet.

The goal (just to summarize) is still to show ALL the members EXCEPT those who have paid.

SOLUTION

Jared_S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jensjakobsen

ASKER

Your statement is right - before it showed 1 (because there was one paying member). Later I have added 7 (so now it's 8).

When I run your statement it shows 8.

So far so good :)

Now I need to "reverse" the statement so that these 8 users are excluded so that I can see who hasn't paid yet?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jared_S

I have nothing to say for myself. Thanks angelIII.

jensjakobsen

ASKER

Thank you very much both of you!!