[RESOLVED] Order By Field From Other Table

Coreye

The problem is that I am trying to join 2 tables and then sort by a certain field.

mysql_query("SELECT * from `offers` WHERE `status` = 'On' ORDER BY `pay_value` ASC");

I need to pull the "pay_value" from 2 different tables and then order it by that only.

The first table is called offers and the field I'm referencing is pay_value type decimal(10,2) and the second table is called exceptions with a pay_value type decimal(10,2).

What I'm doing is showing a list of ads with the amount of money that each ad pays per lead/sale. Some of our clients have been given increased payouts. Well, when they go to sort all of the ads in the system via the 'Pay Value' I can't make it show the increased payouts from the exceptions table while at the same time, being in correct order with the other pay_value's from the offers table.

I've tried the following:

SELECT o.offerid
, o.name, o.incent, o.type, o.reporting, o.category
, IFNULL( e.pay_value, o.pay_value ) AS pay_value
FROM offers AS o
JOIN exceptions AS e USING (pay_value,offerid)
WHERE o.status = 'On'
ORDER BY o.pay_value $orderx LIMIT $from, $max_results

and

SELECT o.offerid
, o.name, o.incent, o.type, o.reporting, o.category
, IFNULL( e.pay_value, o.pay_value ) AS pay_value
FROM offers AS o
left JOIN exceptions AS e using (pay_value,offerid)
WHERE o.status = 'On'
ORDER BY o.pay_value $orderx LIMIT $from, $max_results

Neither have worked. The 1st one seems to only be sorting from the offers table and totally ignoring the exceptions table and second one is returning 0 rows.

Does any one know the problem or know a way to do this?

Thanks,
Corey

tomhath

I don't understand how the second query doesn't return anything if the first one does; double check that it wasn't a data issue or some error you didn't catch.

Change the ORDER BY to this to get the sort you want:

...
ORDER BY IFNULL( e.pay_value, o.pay_value )  $orderx 
LIMIT $from, $max_results

Coreye

tomhath;10901247 wrote:
I don't understand how the second query doesn't return anything if the first one does; double check that it wasn't a data issue or some error you didn't catch.

Change the ORDER BY to this to get the sort you want:
...
ORDER BY IFNULL( e.pay_value, o.pay_value )  $orderx 
LIMIT $from, $max_results

Thanks for the reply 🙂. That did the same thing as the other ones though. We used this to fix it:

SELECT o.offerid 
     , o.name
     , o.incent
     , o.type
     , o.reporting
     , o.category 
     , COALESCE(e.pay_value,o.pay_value) AS sort_pay_value 
  FROM offers AS o 
LEFT OUTER
  JOIN exceptions AS e 
    ON e.offerid = o.offer_id 
 WHERE o.status = 'On' 
ORDER 
    BY sort_pay_value