SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Enthusiast Dolbex's Avatar
    Join Date
    Jun 2003
    Location
    Confused USA
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Subqueries and DISTINCT from mySQL 5 to 4

    Ugh... been working on a project for some time now and I'm just plain stuck and the SQL is getting over my head. I would LOVE any help you can give me. The following query works beautifully fast on my in house machine (mySQL 5) but when I brought it over to the live server (mySQL 4.1) the query takes about 30 seconds to compile... I realize I am not a SQL rock god...

    Code:
    SELECT DISTINCT p.pailid AS PID, p . * , (
    
    SELECT COUNT( * ) AS c
    FROM feed
    WHERE pailid = PID
    AND display =1
    ) AS numFeeds, (
    
    SELECT COUNT( * ) AS c
    FROM comments
    WHERE pailid = PID
    ) AS numComments, c.title AS cattitle, (
    
    SELECT COUNT( * ) AS q
    FROM flags
    WHERE pailid = PID
    AND userid =1
    ) AS isFlagged
    FROM pail p, feed f, category c
    WHERE p.pailid = f.pailid
    AND f.display =1
    AND p.categoryid = c.categoryid
    ORDER BY p.date DESC
    LIMIT 0 , 50
    Should I convert this to JOINS? If so, could someone give me a hand as I am LOST when it comes to JOINS.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    which table is PID in?

    why are you joining to feed in the outer query if you're also counting them in one of the subqueries?

    what happens when you remove DISTINCT?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast Dolbex's Avatar
    Join Date
    Jun 2003
    Location
    Confused USA
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I remove distinct I get multiple returns of the same record.

    PID is pail.pailid

    Think of pails as groups and feeds as items. I want to return the attributes of a group along with a count of how many items there are in each.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Dolbex View Post
    If I remove distinct I get multiple returns of the same record.
    this means you probably shouldn't be joining the feeds table in the outer query

    try this --
    Code:
    SELECT p.pailid 
         , p.date
         , c.title AS cattitle
         , ( SELECT COUNT(*) 
               FROM feed
              WHERE pailid = p.pailid
                AND display = 1       ) AS numFeeds
         , ( SELECT COUNT(*)
               FROM comments
              WHERE pailid = p.pailid ) AS numComments
         , ( SELECT COUNT(*) 
               FROM flags
              WHERE pailid = p.pailid
                AND userid = 1 )        AS isFlagged
      FROM pail p
    INNER
      JOIN category c
        ON c.categoryid = p.categoryid
    ORDER 
        BY p.date DESC LIMIT 50
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast Dolbex's Avatar
    Join Date
    Jun 2003
    Location
    Confused USA
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Fantastic, you hit it right on the head r937. I can't thank you enough. Is there a resource that you can suggest that I can look up to better understand JOINS and SQL in general that you would suggest?

    Thanks so much again r937.

    edit:

    Man, I am just having a tough time wrapping my head around this. I tried porting the solution you gave me to a search query and this is what I came up with:

    Code:
     SELECT p.pailid AS PID, p.date, p.title, p.body, p.tags, p.categoryid, p.feature, c.title AS cattitle, (
    
    SELECT COUNT( * )
    FROM feed
    WHERE pailid = p.pailid
    AND display =1
    ) AS numFeeds, (
    
    SELECT COUNT( * )
    FROM comments
    WHERE pailid = p.pailid
    ) AS numComments, (
    
    SELECT COUNT( * )
    FROM flags
    WHERE pailid = p.pailid
    AND userid =1
    ) AS isFlagged
    FROM pail p, feed f
    INNER JOIN category c ON c.categoryid = p.categoryid
    WHERE MATCH (
    f.title, f.body
    )
    AGAINST (
    'string'
    IN BOOLEAN
    MODE
    ) <>0
    ORDER BY MATCH (
    f.title, f.body
    )
    AGAINST (
    'string'
    IN BOOLEAN
    MODE
    ) DESC , p.date DESC
    LIMIT 0 , 50
    And I get #1054 - Unknown column 'p.categoryid' in 'on clause'. I don't understand - p is defined right above that....
    Last edited by Dolbex; Jun 1, 2007 at 02:34. Reason: new problem

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    why did you add the feed table back to the outer query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast Dolbex's Avatar
    Join Date
    Jun 2003
    Location
    Confused USA
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I thought I needed it for the WHERE clause. If I take it out I get:

    #1054 - Unknown column 'f.title' in 'where clause'

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, i know, but if a pail can have many feeds, which feeds do you want to count?

    at present, your query looks like it wants to return data for all pails that have at least one feed that matches the search, whether or not that feed has display=1, and then for each pail, count the number of feeds that do have display=1

    can you see why i'm confused?

    and i don't even know what a pail is

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast Dolbex's Avatar
    Join Date
    Jun 2003
    Location
    Confused USA
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yea, sorry I am not being more clear.

    Ok, so back to groups and items. What I am trying to do (poorly) is to search through items (feeds) for a string match and sort those matches by their match strength. Because there are many items (feeds) in a group (pail) I want to return distinct groups back in order of the item searches...

    Ideally it would find the SUM of the strength for each group based on the item MATCH and sort DESC by those. Follow?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i was with you right up until the "return distinct groups" part

    what do you consider "strength" of a group?

    and which feeds do you want to count?

    1. only the ones which match the search criteria?
    2. only the ones which match the search criteria and are display=1?
    3. all the feeds in the pail?
    4. only those feeds with display=1?

    you can see why i'm confused, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast Dolbex's Avatar
    Join Date
    Jun 2003
    Location
    Confused USA
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    a. The strength of the group is based on how I understand "MATCH" which can return how 'strong' a search return is - right? If that statement is correct I would like to sort it by this strength measurement in some way so that the 'most accurate' results are at the top.

    b. I The feeds I want to count are the one's that match the search criteria and are display = 1.

    Thanks again for your help r937.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Dolbex View Post
    a. The strength of the group is based on how I understand "MATCH" which can return how 'strong' a search return is - right? If that statement is correct I would like to sort it by this strength measurement in some way ....
    ah, but there is still a problem

    each pail can have several feeds, right?

    what happens if pail A has 2 feeds which match and 42 that don't, while pail B has one feed that matches and none that don't?

    besides, i don't think you can aggregate (add up) match relevancies over a group (although feel free to test this for us, eh )

    but if all you want to do is count them, then ...
    Code:
    SELECT p.pailid AS PID
         , p.date
         , p.title
         , p.body
         , p.tags
         , p.categoryid
         , p.feature
         , c.title AS cattitle
         , ( SELECT COUNT( * )
               FROM feed
              WHERE pailid = p.pailid
                AND display =1
           ) AS numFeeds
         , ( SELECT COUNT( * )
               FROM feed
              WHERE pailid = p.pailid
                AND display =1
                AND MATCH (title,body)   
                    AGAINST ('string' IN BOOLEAN MODE) <>0
           ) AS numMatchingFeeds
         , ( SELECT COUNT( * )
               FROM comments
              WHERE pailid = p.pailid
           ) AS numComments
         , ( SELECT COUNT( * )
               FROM flags
              WHERE pailid = p.pailid
                AND userid =1
           ) AS isFlagged
      FROM pail p
    INNER 
      JOIN category c 
        ON c.categoryid = p.categoryid
    ORDER 
        BY numMatchingFeeds DESC 
         , p.date DESC
    LIMIT 50
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •