Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 451
  • Last Modified:

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?
0
jensjakobsen
Asked:
jensjakobsen
  • 7
  • 7
2 Solutions
 
Jared_SCommented:
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

0
 
jensjakobsenAuthor Commented:
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

0
 
Jared_SCommented:
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
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
jensjakobsenAuthor Commented:
Both suggestions gave the same result.

strPaid is the datatype of nchar.
0
 
Jared_SCommented:
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?
0
 
jensjakobsenAuthor Commented:
They are both "numeric(18, 0)"
0
 
Jared_SCommented:
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

0
 
jensjakobsenAuthor Commented:
Both statements gave me the result of "1".

I do have one member which I have marked as paid (strPaid = 1)
0
 
Jared_SCommented:
Check my last post again. It hit me right after I posted, so I edited my comment - apparently not fast enough though.
0
 
jensjakobsenAuthor Commented:
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.
0
 
Jared_SCommented:
This is killing me because what you're asking for is routine. It's just a join between two tables with some filtering criteria in one table. I'm not asking the right questions.

Please run this

select count(*) from tbl01User, tbl055PaidMembers where tbl01User.pkIntUserID = tbl055PaidMembers.fkIntUserID

Open in new window


The results should be the count of all of the records in these two tables where the User Id's match. If the result is anything less than what you visually see in your tables, please post the table schema's and/or one or two rows of test data.
0
 
jensjakobsenAuthor Commented:
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?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the subquery is wrong, you don't need to repeat the tbl01user table there ...
select * from tbl01User
where not exists (select null from tbl055PaidMembers where tbl01User.pkIntUserID = tbl055PaidMembers.fkIntUserID and isnull(tbl055PaidMembers.strPaid,0) = '1');
                                            

Open in new window

0
 
Jared_SCommented:
I have nothing to say for myself. Thanks angelIII.
0
 
jensjakobsenAuthor Commented:
Thank you very much both of you!!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now