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

    Unanswered: help on filtering results

    I have three tables listed below

    TBLINFO
    INFID NAME

    1 JOHN
    2 PAUL
    3 WILLIAM


    TBLPUBS
    PUBID LANGUAGE
    30 ENGLISH
    31 SPANISH
    32 FRENCH
    33 ITALIAN

    TBLCOUNT
    PUBCOUNTID INFID PUBID PUBCOUNT

    1 1 30 2
    2 1 31 1
    3 2 30 2
    4 2 30 2


    THE RESULTS SHOULD BE
    name pubcount
    PAUL 2
    WILLIAM 2

    Paul and william came up because they are the two that satisfies the criteria which is to give me all the persons which appears in tblcount wherein infid appears only once and having pubcount of more than one (1)

    I came up with the sql query below but


    SELECT *

    FROM tblinfo INNER JOIN tblpubs ON infid=infid INNER JOIN tblcount nON pubid=pubid

    GROUP BY pubid, pubcount

    HAVING pubid = 30 AND (p.pubcount > 1)

    thanks for any help i am really stucked with this

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    william does not appear in tblcount, so your example doesn't make sense
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2006
    Posts
    87

    correction on tblcount

    Hi Rudy

    Thanks for that the tblcount table should be this the fourth record of tblcount should have the infid of william which is 3

    TBLCOUNT
    PUBCOUNTID INFID PUBID PUBCOUNT

    1 1 30 2
    2 1 31 1
    3 2 30 2
    4 3 30 2

    again thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "give me all the persons which appears in tblcount wherein infid appears onlòy once and having pubcount of more than one (1)"
    Code:
    SELECT infid
      FROM tblcount
     WHERE pubcount > 1
    GROUP
        BY infid
    HAVING COUNT(*) = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2006
    Posts
    87
    THANKS RUDY

    GO THE RESULTS I NEED



    Quote Originally Posted by r937
    "give me all the persons which appears in tblcount wherein infid appears only once and having pubcount of more than one (1)"
    Code:
    SELECT infid
      FROM tblcount
     WHERE pubcount > 1
    GROUP
        BY infid
    HAVING COUNT(*) = 1

Posting Permissions

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