A Better Way (Complicated Select Statement)

After hours of trying, I finally got a select statement to return what I needed, but I am not sure how I am doing is the most efficient way. Please give me your input.

Here's the situation: I want to allow customers to add to their magazine subscriptions online, so I created an aspx form that shows the magazines they are currently subscribed to and allows them to choose from other "available" magazines. In the available magazines field, I want to show all magazines that we have minus the ones the customer is already subscribed to.

MY pb table lists all of the magazines. The info table lists customers and their current magazine subscriptions.

I also have a table, PBExclusive that is for magazines that we have, but only want to be available to certain customers. So, I also need to make sure the "available" magazines field doesn't list any of the exclusions unless the customer is listed as the exception for that magazine.

Here is what I have:

("SELECT p.code, p.WebName FROM pb p WHERE (p.code IN(Select e.code FROM PBExclusive e WHERE e.id = " & lblID.Text & ") OR p.code NOT IN(Select e.code From PBExclusive e Where e.code = p.code)) AND p.code NOT IN (SELECT i.code FROM info i, pb p WHERE i.id = " & lblID.Text & " AND i.code = p.code)AND p.contract <> '1' AND p.code <> '00' AND WebListing <> '0' AND p.code <> '' AND p.code <> 'SU' AND p.code<> 'AGC' ORDER BY p.WebName", conn)

Hi J,

Do you have a table that includes customers only?

Does your PBExclusive join the customers to the PB table with all the magazines?

To get the list of the magazines a customer doesn't have already, Left Join the PB list of magazines to the Info table using "...Where Info.Whatever Is Null..." It's harder to eliminate the exclusive magazines without more information about what this table looks like and how it relates to the others.