SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    3MTA3
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    1,016
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sql help - filtering data betwen two tables

    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?

  2. #2
    Web Genius
    Join Date
    Nov 2001
    Location
    Canada
    Posts
    708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try something like

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


  3. #3
    3MTA3
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    1,016
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ramprage
    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.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    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.

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...
    Code:
    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...

  6. #6
    3MTA3
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    1,016
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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...
    Code:
    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.

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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!

  8. #8
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    not a problem man, let me know if you need anything else.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •