Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Inner Join Tables and Order By

Posted on 2007-08-10
7
Medium Priority
?
1,925 Views
Last Modified: 2012-05-05
I have three mysql tables as below.

field_1_id can have many field_3_id's

I would like to select field_1_text and order by the total of all the field_3_number's for each field_1_id. I would also like to return this total.

I am only interested in returning one row for each field_1_id.

table_1
field_1_id
field_1_text

table_2 (pivot)
field_2_1_id
field_2_3_id

table_3
field_3_id
field_3_stuff
field_3_number

field_1_id = field_2_1_id
field_2_3_id = field_3_id

What I have at the moment is (it doesn't work because it has pseudo in it):

SELECT field_1_text, sum(field_3_number for each field_1_id) as total_number

FROM table_1

INNER JOIN table_2 field_1_id = field_2_1_id

INNER JOIN table_3 field_3_id = field_2_3_id

ORDER BY sum(field_3_number for each field_1_id) ASC
0
Comment
Question by:ncoo
  • 4
  • 3
7 Comments
 
LVL 39

Accepted Solution

by:
appari earned 2000 total points
ID: 19670049
SELECT field_1_id, field_1_text, sum(field_3_number) as total_number
FROM table_1
INNER JOIN table_2 field_1_id = field_2_1_id
INNER JOIN table_3 field_3_id = field_2_3_id
Group by field_1_id, field_1_text
ORDER BY 3 asc

or

SELECT field_1_id, field_1_text, sum(field_3_number) as total_number
FROM table_1
INNER JOIN table_2 field_1_id = field_2_1_id
INNER JOIN table_3 field_3_id = field_2_3_id
Group by field_1_id, field_1_text
ORDER BY sum(field_3_number)  asc
0
 
LVL 15

Author Comment

by:ncoo
ID: 19670150
Thanks that does seem to work with my test data.

With ORDER BY 3 does the number 3 simply refer to sum(field_3_number) being the 3rd select request, so saves SQL having to calculate SUM twice?

Will give this a test in a few minutes on real data.
0
 
LVL 39

Expert Comment

by:appari
ID: 19670180
yes number 3 indicates the column position in the select list.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 15

Author Comment

by:ncoo
ID: 19670323
Thanks with the Group By do I list every select except the ones which are SUM or can I just list field_1_id?
0
 
LVL 39

Expert Comment

by:appari
ID: 19670452
yes you have to either use aggregate function or include the field in the group by clause.
0
 
LVL 15

Author Comment

by:ncoo
ID: 19670524
I don't fully understand this aggregate function would it be:

SELECT field_1_id, field_1_text, sum(field_3_number) as total_number
FROM table_1
INNER JOIN table_2 field_1_id = field_2_1_id
INNER JOIN table_3 field_3_id = field_2_3_id
Group by sum(field_3_number)
ORDER BY sum(field_3_number) asc

With ORDER BY 3 is there a way to always select the last column? Or is the logic is SQL clever enough to only do sum(field_3_number) one time?
0
 
LVL 39

Expert Comment

by:appari
ID: 19670557
it will do only once,
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question