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

    Unanswered: help on adding another filter statement on the query

    Hi everybody have this query that list all the duplicate records meaning the occurence of pubinfid in more than one row. This gets me fine. Iwant thought to insert a where clause that further filters the result where pubid between 30 and 33. I tried placing it after the FROM CLAUSE BUT DOESN'T GIVE me what I want it still includes records other than the between clause. Also I tried placing it after the HAVING clause but it includes records which has only one count.. Where could I place the where clause or is ther an alternative for this.



    SELECT a.pubinfid, a.pubid, a.pubcount
    FROM pubssubscribe AS a INNER JOIN
    (SELECT pubinfid
    FROM pubssubscribe
    GROUP BY pubinfid
    HAVING (COUNT(*) > 1)) AS b ON a.pubinfid = b.pubinfid



    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT a.pubinfid
         , a.pubid
         , a.pubcount
      FROM pubssubscribe AS a 
    INNER 
      JOIN ( SELECT pubinfid
               FROM pubssubscribe
             GROUP 
                 BY pubinfid
             HAVING COUNT(*) > 1 ) AS b 
        ON b.pubinfid = a.pubinfid
     WHERE a.pubid BETWEEN 30 AND 33
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2006
    Posts
    87
    Quote Originally Posted by r937
    Code:
    SELECT a.pubinfid
         , a.pubid
         , a.pubcount
      FROM pubssubscribe AS a 
    INNER 
      JOIN ( SELECT pubinfid
               FROM pubssubscribe
             GROUP 
                 BY pubinfid
             HAVING COUNT(*) > 1 ) AS b 
        ON b.pubinfid = a.pubinfid
     WHERE a.pubid BETWEEN 30 AND 33

    With the above solution which I already tried it before.. it gives me the wrong result meaning it doesn't take into conside the having count(*)>1 because it gives me records which has no duplicates what I need are records which has duplicates on pubinfid..

    again thanks

  4. #4
    Join Date
    Aug 2006
    Posts
    87
    With the above solution which I already tried it before.. it gives me the wrong result meaning it doesn't take into conside the having count(*)>1 because it gives me records which has no duplicates what I need are records which has duplicates on pubinfid..

    again thanks

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, then try this:
    Code:
    SELECT a.pubinfid
         , a.pubid
         , a.pubcount
      FROM pubssubscribe AS a 
    INNER 
      JOIN ( SELECT pubinfid
               FROM pubssubscribe
              WHERE pubid BETWEEN 30 AND 33
             GROUP 
                 BY pubinfid
             HAVING COUNT(*) > 1 ) AS b 
        ON b.pubinfid = a.pubinfid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2006
    Posts
    87
    Quote Originally Posted by r937
    okay, then try this:
    Code:
    SELECT a.pubinfid
         , a.pubid
         , a.pubcount
      FROM pubssubscribe AS a 
    INNER 
      JOIN ( SELECT pubinfid
               FROM pubssubscribe
              WHERE pubid BETWEEN 30 AND 33
             GROUP 
                 BY pubinfid
             HAVING COUNT(*) > 1 ) AS b 
        ON b.pubinfid = a.pubinfid

    with this second solution it considers the having count(*)>1 and only displays the records which has duplicate rows based on pubinfid. The problem with this result though is it includes rows with the pubid even if it is not on the range of pubid between 30 and 33 example it gives me rows of pubid 14, 17 etc meaning it is not considering the WHERE pubid between 30 and 33 clause

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ummm, there's something you're not telling us.

    Provide sample data and required output please.
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, then try this:
    Code:
    SELECT a.pubinfid
         , a.pubid
         , a.pubcount
      FROM pubssubscribe AS a 
    INNER 
      JOIN ( SELECT pubinfid
                  , pubid  
               FROM pubssubscribe
              WHERE pubid BETWEEN 30 AND 33
             GROUP 
                 BY pubinfid
                  , pubid  
             HAVING COUNT(*) > 1 ) AS b 
        ON b.pubinfid = a.pubinfid
       AND b.pubid    = a.pubid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2006
    Posts
    87
    Hi the third result gave me zero records. I summarized everything down with the sql and the result...

    thanks again

    below is the results of the first sql

    SELECT a.pubinfid
    , a.pubid
    , a.pubcount
    FROM pubssubscribe AS a
    INNER
    JOIN ( SELECT pubinfid
    FROM pubssubscribe
    GROUP
    BY pubinfid
    HAVING COUNT(*) > 1 ) AS b
    ON b.pubinfid = a.pubinfid
    WHERE a.pubid BETWEEN 30 AND 33
    order by a.pubinfid
    here it respects the where clause giving pubid between 30 and 33 but it gets records that has no duplicates like pubinfid 5, 8, 9, 13, etc....
    pubinfid pubid pubcount
    1 |30 | 1
    1 |31 |1
    5 |32 |1
    8 |32 |1
    9 |33 |2
    13 |33 |1
    16 |30 |1
    20 | 33 |3
    23 |33 |1

    this is the results of the second sql
    SELECT a.pubinfid
    , a.pubid
    , a.pubcount
    FROM pubssubscribe AS a
    INNER
    JOIN ( SELECT pubinfid
    FROM pubssubscribe
    WHERE pubid BETWEEN 30 AND 33
    GROUP
    BY pubinfid
    HAVING COUNT(*) > 1 ) AS b
    ON b.pubinfid = a.pubinfid
    order by a.pubinfid

    here it respects the HAVING COUNT(*) > 1 ) getting only the records with pubid that has duplicates but it gives me all the pubid which should only get from 30 to 33

    [pubinfid pubid pubcount
    1 | 17 | 1
    1 | 18 | 1
    1 | 3 | 1
    25 | 14 | 1
    25 | 29 | 3
    25 | 30 | 1
    29 | 14 | 1
    29 | 29 | 3


    and the third sql gives me 0 records

    SELECT a.pubinfid
    , a.pubid
    , a.pubcount
    FROM pubssubscribe AS a
    INNER
    JOIN ( SELECT pubinfid
    , pubid
    FROM pubssubscribe
    WHERE pubid BETWEEN 30 AND 33
    GROUP
    BY pubinfid
    , pubid
    HAVING COUNT(*) > 1 ) AS b
    ON b.pubinfid = a.pubinfid
    AND b.pubid = a.pubid
    order by a.pubinfid

    the required results should be that it only gets the pubid 30, 31, 32, 33
    which has more than one records based on pubinfid.. below is a sample result



    pubinfid pubid pubcount

    1 30 2
    1 31 1
    5 32 1
    5 33 1

    etc.
    Last edited by alexiop; 05-28-08 at 03:54.

Posting Permissions

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