SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can someone translate this SQL for me?

    I've got this query which was originally associated with an Access db. I'm now using mysql which does not support subqueries (yet). I'm just not sure how to convert it to a mysql safe query. Can someone give me a hand?

    Code:
    SELECT DISTINCT User_ID, (
    	SELECT Count(User_ID) as test
    	FROM Users p1
    	WHERE p1.User_ID = users.User_ID
    ) AS CountID
    FROM Users
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, here's how it works

    for each row in the Users table (outer query), it gets the Userid

    then it uses this as the correlation variable in a correlated subquery

    the subquery gets every row from a "separate copy" of the table (except it's really the same table, but never mind that for now) which has the same userid as the userid of that row we were looking at in the outer query

    see how the correlation variable works? it joins all the rows of the inner query to the one row of the outer query

    okay, having found all the rows of the inner table which have the same userid as the row of the outer table, it counts those inner rows

    now, you're probably wondering, if the same userid is in the table more than once, won't it do the same thing for each one?

    yup

    and that's why whoever wrote this had to use DISTINCT, so that all those duplicate repetitions would be eliminated

    in summary: whoever wrote this didn't have a clue what he or she was doing

    what you want is this:
    Code:
    select User_ID
         , count(*) as test
      from Users
    group
        by User_ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Whew...thanks Rudy.

    I'm using this poll system that I found online but I'm having to convert it. What a pain.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  4. #4
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A quick question. Now it loosk like the variable CountID is showing up somewhere further down in the code. How would I get that back?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  5. #5
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Never mind, I got it.
    Code:
    SELECT User_ID as CountID, COUNT(*) AS test
    FROM tblpollusers
    GROUP BY User_ID
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    change test to CountID in the query i gave you

    and be careful, okay?

    if the rest of the script is anything like this, i wouldn't trust it at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no, dude, the other way around!
    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
  •