Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Jul 2003
    Location
    New Zealand
    Posts
    435
    Thanks
    1
    Thanked 0 Times in 0 Posts

    mysql question retreiving data from other tables

    hi,

    i have a table with some numbers, my original query was:
    SELECT * FROM table ORDER BY (field1/field2)

    i have another table that has rows in it, each with a field1 and field2 value, and i found that each of those columns corresponds to total up to the field1 and field2 in my query, so i could sum(field1) and sum(field2) and use that instead of having a seperate column in the first table.

    how can i do this? that's pretty much my question. i want something like this (no idea about the syntax- help!? please):

    SELECT * FROM table ORDER BY ( SUM(othertable.field1) / SUM(othertable.field2) )

    any help? cheers,
    robbie

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Assuming you are using version 4.1+, you can use subqueries in your order by clause. You just need a column to join the two tables on.

    Code:
    SELECT a.field
    FROM table_a as a
    ORDER BY (select sum(b.field) from table_b as b where a.keyfield = b.keyfield)
    ,(select sum(c.field) from table_c as c where a.keyfield = c.keyfield)

  • #3
    Regular Coder
    Join Date
    Jul 2003
    Location
    New Zealand
    Posts
    435
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fumigator View Post
    Assuming you are using version 4.1+, you can use subqueries in your order by clause. You just need a column to join the two tables on.

    Code:
    SELECT a.field
    FROM table_a as a
    ORDER BY (select sum(b.field) from table_b as b where a.keyfield = b.keyfield)
    ,(select sum(c.field) from table_c as c where a.keyfield = c.keyfield)
    mint awesome, thanks

  • #4
    Regular Coder
    Join Date
    Jul 2003
    Location
    New Zealand
    Posts
    435
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Scrowler View Post
    mint awesome, thanks
    hey, don't really understand how to implement this. can you help me out?

    i want to select * from "Teams" and order by [FramesFor/(FramesAgainst+FramesFor) from "Players"]

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    why not show your actual tables in the first place? then you won't have to try and implement something that someone had to guess at because you didn't give actual tables/columns.

  • #6
    Regular Coder
    Join Date
    Jul 2003
    Location
    New Zealand
    Posts
    435
    Thanks
    1
    Thanked 0 Times in 0 Posts
    ok, i've got what i want to be achieving to work, but i'm sure it can be optimised. how can i cut it down?

    Code:
    SELECT * FROM Teams as a ORDER BY 
    ( 
    SELECT SUM(FramesFor) FROM Players WHERE TeamID = a.id ) /
    ( ( SELECT SUM(FramesFor) FROM Players WHERE TeamID = a.id ) + ( SELECT SUM(FramesAgainst) FROM Players WHERE TeamID = a.id ) )
    DESC

  • #7
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Honestly to cut it down then you wouldn't sort it like that-- you'd dump the data into an array (int the script language you are using) and then manipulate the data in the array (sort, calculate, etc).

  • #8
    Regular Coder
    Join Date
    Jul 2003
    Location
    New Zealand
    Posts
    435
    Thanks
    1
    Thanked 0 Times in 0 Posts
    i had thought about doing that, but had thought i could just use the query. as i'm not too familiar with mysql would you say it would be faster to use that query or faster to use php?


  •  

    Posting Permissions

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