SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy how to do this using sql ?

    hi,

    i'm try to fetch and count table at the same time using sql and smarty template system,.

    i have table with name "one" and it's contain two columns :
    - id
    - name

    i fetched it using select statment :
    PHP Code:
    mysql_query("SELECT * FROM one"
    and fetch result using mysql_fetch_array

    ,

    now the table "one" is shared with another table (it's name "two")
    and i want count the id rows in "one" table (which is shared in "two table") in "two" table.

    i tried to use this style of sql
    PHP Code:
    SELECT one.id,name.id,COUNT(oneid) as co FROM one,two WHERE one.id=oneid 
    but it didn't work ,

    so help me with true command to this matter

    Thanks

  2. #2
    Non-Member DelvarWorld's Avatar
    Join Date
    Jul 2004
    Location
    Baloney
    Posts
    341
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could go with a PHP solution http://us.php.net/mysql_num_rows

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi

    yes i know ,
    but i want to collect all in one sql sommand only

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT one.id
         , one.name
         , COUNT(two.oneid) as co 
      FROM one
    LEFT OUTER
      JOIN two 
        ON two.oneid = one.id
    GROUP
        BY one.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Mr r937

    please Accept My Admiration For Your Big experiences
    it's worked ,
    thaks u very very much

    ** External Question **: can u advise me if possible with good book to improve my information in using sql and dealign with mysql ?

    Thanks Again

  6. #6
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT one.id
         , one.name
         , COUNT(two.oneid) as co 
      FROM one
    LEFT OUTER
      JOIN two 
        ON two.oneid = one.id
    GROUP
        BY one.id
    Mr Mr r937 , sorry but i want small help from u , can u rewrite the sql command if i added "three" table to select from two and three tables ?

    it tried like :
    Code:
    SELECT one.id
         , one.name
         , COUNT(two.oneid) as co ,COUNT(three.oneid) as se
      FROM one
    LEFT OUTER
      JOIN (two,three)
        ON two.oneid = one.id AND three.oneid=one.id
    GROUP
        BY one.id
    but it returned 0 result ,

    so can u rewrite the true command

    Thanks Again

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    for three tables, it gets messier
    Code:
    SELECT one.id
         , one.name
         , t2.count2 AS co 
         , t3.count3 AS se
      FROM one
    LEFT OUTER
      JOIN ( SELECT oneid
                  , COUNT(*) AS count2
               FROM two
             GROUP
                 BY oneid ) as t2
        ON t2.oneid = one.id
    LEFT OUTER
      JOIN ( SELECT oneid
                  , COUNT(*) AS count3
               FROM three
             GROUP
                 BY oneid ) as t3
        ON t3.oneid = one.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    for three tables, it gets messier
    Code:
    SELECT one.id
         , one.name
         , t2.count2 AS co 
         , t3.count3 AS se
      FROM one
    LEFT OUTER
      JOIN ( SELECT oneid
                  , COUNT(*) AS count2
               FROM two
             GROUP
                 BY oneid ) as t2
        ON t2.oneid = one.id
    LEFT OUTER
      JOIN ( SELECT oneid
                  , COUNT(*) AS count3
               FROM three
             GROUP
                 BY oneid ) as t3
        ON t3.oneid = one.id

    Mr r937 , really u are very intelligent ,

    yes it's worked very fine , now i want only other one help in this thread ,

    i want change tables selecting process , like :
    i want to fetch one.name from "two" table which contain column "oneid",
    and fetch number of rows of table "three" which contain twoid column

    i tried like

    Code:
    SELECT two.id,two.name,one.name AS boname,COUNT(three.twoid) as hadno FROM two LEFT OUTER join (one,three) ON three.twoid=two.id GROUP BY three.id ORDER BY id ASC
    But it return double of number not the true number

    Yoyr Help Plz

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by e.2512 View Post
    i want change tables selecting process , like :
    i want to fetch one.name from "two" table which contain column "oneid",
    and fetch number of rows of table "three" which contain twoid column
    i am completely confused

    good luck

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

  10. #10
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i am completely confused

    good luck


    Mr r937 , u are my hope

    i know that it's very confused , i will try to make it mor simple

    at first i select from "two" table and "three" table using "one" table
    now i want select from "one" table and "three" table using "two" table

    i tried using this command :
    Code:
    SELECT two.id,two.name,one.name AS boname,COUNT(three.twoid) as hadno FROM two LEFT OUTER join (one,three) ON three.twoid=two.id GROUP BY three.id ORDER BY id ASC
    and it's work good , but the probem that it return double of number of counting(three.twoid) as hadno

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by e.2512 View Post
    the probem that it return double of number of counting(three.twoid) as hadno
    did you see how i used subqueries to calculate separate totals before joining to the main table?

    this was to avoid just exactly the problem you are having

    try writing your new query the same way

    i can't help you any further because of the "fake" names
    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
  •