sql help - filtering data betwen two tables

[devised]

I am having problems trying to figure out how to filter results from one table based on another table. In the project, many sites will be using the same database, but the results will be filterd for each site. I'm using asp/sql and have two tables in a database that are set similiar to this:

LinkSite
======
SiteId
SiteUrl
SiteTitle

LinkFilterSite
==========
FilterId
SiteId
FilterSiteId

I want to display all the sites from the LinkSite table, except the ones that are set to be filtered in the second table by the field "FilterSiteId".

Is there a type of join I could use to achieve this?

[ramprage]

Try something like

select * from LinkSite,LinkFilterSite
WHERE LinkSite.SiteId=LinkFilterSite.SiteId AND SiteId!=FilterSiteId

[devised]

Try something like

select * from LinkSite,LinkFilterSite
WHERE LinkSite.SiteId=LinkFilterSite.SiteId AND SiteId!=FilterSiteId

ramprage - thanks for the attempt, but no luck

Doing that will display the site that is to be filtered 3 times (which is the number of sites that are set to be filtered for that site).


Let me try to explain a little better how I have it set up to see if mabey there's a better way to approach it.

1-The script (a link directory) is installed on many sites.

2-There's a master site (or admin site) that controls which links are to be displayed for each site. This is achieved by setting up a table (LinkFilterSite) that will hold the siteId and the id of the site that you do not want to display for that site (FilterSiteId).

3-When a user visits a site's link directory, the script will grab the id of the site the script is on (which will be set when the script is setup on the site), pull the links from the master database and filter them based on the links that are set to be filtered, and then display the results. This should (hopefully) prevent from showing links to competitors sites on the site the script is installed on.

Am I going about this right? Is there a better way?

I'm usually verbally challenged the first half of the week so sorry if I confuse anyone.

[longneck]

select l.*
from linksite l
join linkfiltersite f on
 f.siteid = l.filtersiteid
where l.siteid = $siteid;

substitue $siteid with the id of the site that the directory script is on.

[null]

Still confused on what you're looking for. Let me stab...So if LinkSite.SiteID is set to be filtered (or simply in LinkFilterSite.FilterSiteID) anytime, then you don't want that SiteID returned in your query...is that right? If so...it's not terribly hard, a subquery would work ok in this case...

Select * from Linksite
where  SiteID not in
          (Select distinct FilterSiteID
           from   LinkFilterSite)

...am I on? I just really don't totally understand the criteria I guess...

[devised]

Still confused on what you're looking for. Let me stab...So if LinkSite.SiteID is set to be filtered (or simply in LinkFilterSite.FilterSiteID) anytime, then you don't want that SiteID returned in your query...is that right? If so...it's not terribly hard, a subquery would work ok in this case...

Select * from Linksite
where  SiteID not in
          (Select distinct FilterSiteID
           from   LinkFilterSite)

...am I on?

Thanks null, you were dead on. That was exactly what I was looking for.

[longneck]

This should (hopefully) prevent from showing links to competitors sites on the site the script is installed on.

doh! mine did it the other way around. it will only show the sites you wanted to filter out!

thank you, null, for posting some good code!

[null]

not a problem man, let me know if you need anything else.