Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2006
    Posts
    87

    Unanswered: help with results

    Hi everybody

    I have two tables which are info and pubssubcribe. each record on the info table has a corresponding subscription on the pubssubscribe table. I need to extract those records on the pubssubscribe with the infid appearing more than once and having the pubid BETWEEN 19 AND 22 and count the records grouped on infid
    couldn't get thru with the code below

    Here's my sql code
    SELECT info.infid, info.infname
    FROM info INNER JOIN
    pubssubscribe ON info.infid = pubssubscribe.pubinfid
    WHERE (info.infcond IS NULL) AND (pubssubscribe.pubid BETWEEN 19 AND 22 AND pubssubscribe.pubid > 1) AND (info.infid > 1)
    ORDER BY info.infid

    thanks

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by alexiop
    SELECT info.infid, info.infname
    FROM info INNER JOIN
    pubssubscribe ON info.infid = pubssubscribe.pubinfid
    WHERE (info.infcond IS NULL) AND (pubssubscribe.pubid BETWEEN 19 AND 22 AND pubssubscribe.pubid > 1) AND (info.infid > 1)
    ORDER BY info.infid
    Im feeling generous today ...

    Code:
     
    SELECT info.infid, count(*)
    FROM info 
    INNER JOIN pubssubscribe ON info.infid = pubssubscribe.pubinfid
    WHERE (info.infcond IS NULL) AND (pubssubscribe.pubid BETWEEN 19 AND 22 AND pubssubscribe.pubid > 1) AND (info.infid > 1)
    GROUP BY info.infid
    HAVING count(*) > 1

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Aug 2006
    Posts
    87
    thanks tom for your generosity got it right...

Posting Permissions

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