0

I have joined a few tables together and all seems to work well..

SELECT * FROM products
JOIN underwriters ON (products.underwriter_lookup = underwriters.id_uw)
JOIN countries ON (products.country_lookup = countries.id_co)
JOIN repay_types ON (products.repay_type_lookup = repay_types.id_repay)
JOIN base_rates ON (products.base_rate_lookup = base_rates.base_rates_id)

However, I have been asked if I can now sort some of the output into numerical order.

There is a row in the "Products" table called "initial_rate" which is an INT
In the "Base Rates" Table "base_rates_amount" which is also an INT

What I need to do is sort the query kind of like this to give a total interest rate..

ORDER BY products.initial_rate + base_rates.base_rates_amount

But this doesn't work.

I have been scouring Google for solutions, but the ones I have found are too simplistic and don't include JOINS etc..

This is just beyond my experience right now and would really appreciate any pointers.

Thanks

Mike

2
Contributors
4
Replies
5
Views
8 Years
Discussion Span
Last Post by mikenco
0

No, I can't get any results when using ORDER BY at all..

ORDER BY products.initial_rate
ORDER BY (products.initial_rate)
ORDER BY `products.initial_rate`
0

Ok, I kinda got it working with this.. but it only pulls a single record, but there are at least 10 'best buys'..

$bareQuery = "SELECT * FROM (products)
				JOIN underwriters ON (products.underwriter_lookup = underwriters.id_uw)
				JOIN countries ON (products.country_lookup = countries.id_co)
				JOIN repay_types ON (products.repay_type_lookup = repay_types.id_repay)
				JOIN base_rates ON (products.base_rate_lookup = base_rates.base_rates_id)
				WHERE (products.best_buy = '1')
				ORDER BY SUM(products.initial_rate+base_rates.base_rates_amount)
				";

Any idea why it is displaying only one record instead of all of them?

0

RESOLVED!.. I simply dropped off the SUM part and it now works.. go figure :)

ORDER BY (products.initial_rate+base_rates.base_rates_amount) LIMIT 0, 20
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.