SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    site_ids that a specific content_id not submitted to (was "Query syntax question")

    I'm trying to write a query and I've hit a dead end. It should be simple, but apparently I've burned one to many brain cells.

    Here's the simple database:

    content_id site_id
    1 1
    1 2
    1 3
    2 1
    2 2
    3 1
    3 3


    Each content_id can be related to multiple site_ids. I want a query that finds all the unique site_ids that a specific content_id has not been submitted to. The site_ids can be repeated several times in this table, but there is only one potential occurence of a site_id for each content_id.

    Anyway, it is very easy to find the sites that I have submitted a particluar content_id to. It is also easy to find a list of unique site_ids. If there were the opposite of UNION (EXCLUSION?) or something like that it would be easy to just combine those two statements. Maybe I'm thinking too hard...

    For this simple database, one query might look for site_ids that content_id#2 has not been submitted to. It should return site_id 2.

    Thanks in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if you have a table of sites, use it instead of the derived table distinct_s --
    Code:
    select distinct_s.site_id
      from (
           select distinct site_id
             from simpletable
           ) as distinct_s
    left outer
      join simpletable as s
        on s.site_id = distinct_s.site_id
     where s.site_id is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, but I think maybe my explanation might not have been very clear. I said "database" instead of "table". Anyway, I have two fields in one single table. One field is called content_id and the other is called site_id. My query needs to return all site_ids that do not have a specific content_id tied to them. That would be easy, except that a given site_id can be associted with many content_ids. So you can't do a simple:

    SELECT distinct site_id FROM mysimpletable WHERE content_id<>$mycontentid


    Since many content_ids have the same site_ids, that would return too many sites. I basically want to eliminate any site_ids associted with the specific content_id that I pass to the query.

    I don't fully understand what your code is doing, but I do know that it doesn't involve content_id at all, so I don't think it is doing what I need.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by whitemank
    My query needs to return all site_ids that do not have a specific content_id tied to them.
    Code:
    select distinct_s.site_id
      from (
           select distinct site_id
             from simpletable
           ) as distinct_s
    left outer
      join simpletable as s
        on s.site_id = distinct_s.site_id
       and s.content_id = 2
     where s.site_id is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are the man! Thanks!


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
  •