SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    the mother of all queries

    I have this query for MS SQL that is supposed to query the master table of articles and insert the 'feature' article into a new table.[vbs]
    INSERT INTO ltw_feature ( title, article, source, url, Author, bio, authorurl, authorurl2 )
    SELECT TOP 1 ltw_articles.title, ltw_articles.content, ltw_articles.sourcename, ltw_articles.sourceURL, ltw_authors.aname, ltw_authors.bio, ltw_authors.authorURL, ltw_authors.authorURL2
    FROM ltw_articles
    INNER JOIN ltw_authors
    ON ltw_articles.authorID = ltw_authors.authorref ORDER BY newid()[/vbs]

    Now my problem is, is that I don't know how to make sure that the same article hasn't already been previously entered into the db. Any way to limit what gets selected to articles NOT already in the feature article table?

    Thanks.
    Sketch
    Aaron Brazell
    Technosailor



  2. #2
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Newcastle, England
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could add a sub query to your select statement, using EXISTS (or rather, NOT EXISTS) to check for the article.

    How you determine whether two articles are the "same" is up to you to decide of course, but say for example that your criteria was the titles being the same. Then you'd modify your query to read:

    Code:
    INSERT INTO ltw_feature f1 ( title, article, source, url, Author, bio, authorurl, authorurl2 ) 
    Select TOP 1 ltw_articles.title, ltw_articles.content, ltw_articles.sourcename, ltw_articles.sourceURL, ltw_authors.aname, ltw_authors.bio, ltw_authors.authorURL, ltw_authors.authorURL2 
    FROM ltw_articles 
       INNER Join ltw_authors 
          On ltw_articles.authorID = ltw_authors.authorref 
       WHERE NOT EXISTS (SELECT title FROM ltw_feature f2 WHERE f2.title = ltw_articles.title)
    ORDER BY newid()
    So the query will now only get records where there isn't already a feature with the same title.

    Hope that helps .
    Nick Wilson [ - email - ]


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
  •