...

View Full Version : mysql question retreiving data from other tables



Scrowler
03-25-2007, 10:46 AM
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

Fumigator
03-25-2007, 07:17 PM
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.



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)

Scrowler
03-26-2007, 11:16 AM
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.



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

Scrowler
03-29-2007, 10:48 AM
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"]

guelphdad
03-29-2007, 02:37 PM
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.

Scrowler
04-03-2007, 04:48 AM
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?


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

Fumigator
04-04-2007, 07:06 AM
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).

Scrowler
04-04-2007, 10:14 AM
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?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum