[SOLVED] PHP + MySQL Order By Field From Other Table??

[PeerFly]

I'm really getting frustrated about this issue. The problem is that I am trying to join 2 tables and then sort by a certain field.

 

I don't know enough about joins to make this work and I've googled until my head exploded.

 

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.

 

Anyone?

 

[fenway]

how about some table structure?

[PeerFly]

Ok, 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 to our clients 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 hope that doesn't sound too confusing.

 

[fenway]

So you want to show both prices?

[PeerFly]

I just want to join the tables and sort by the pay_value. But yeah, I want the prices from both tables to show.

[fenway]

It's far from ideal to join on a non-integer value -- particularly on a variable value, like price.

 

select o.pay_value as orig, 
e.pay_value as new 
from offers as o
left join exceptions as e using ( pay_value )
where o.status = 'On' order by o.pay_value ASC

[PeerFly]

Ok fen, you got me started in the right direction, thank you!

 

One thing I didn't mention was the pay_value from the exceptions table only needs to show if the offerid is shown in the exceptions table. I modified the sql a bit, but it doesn't seem to work...

 

select o.offerid, o.name, o.incent, o.type, o.reporting, o.category, o.pay_value as orig, e.pay_value as new from offers as o left join exceptions as e using (pay_value) where o.status = 'On' and e.offerid = o.offerid order by o.pay_value $orderx LIMIT $from, $max_results

 

I added in more selected fields:

 

o.offerid, o.name, o.incent, o.type, o.reporting, o.category

 

And:

 

e.offerid = o.offerid

 

It seems like it would work but the result is no fields are shown even though there are 3 payout exceptions in the exceptions table.

 

 

[PeerFly]

Anyone? Fenway?

[fenway]

Try:

 

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

[PeerFly]

Ok, well... no errors but it seems to only be sorting from the offers table and totally ignoring the exceptions table. It's acting like this:

 

select * from `offers` where `status` = 'On' ORDER BY `pay_value` $orderx LIMIT $from, $max_results

[PeerFly]

Can anyone else help with the problem? Fenway seems like a busy man but I'm sure there are more mysql know-it-alls on this board!

[PeerFly]

Is this even possible? Should I sort this row with javascript and give up on the mysql sorting of this row?

[fenway]

Now i'm confused... what are you trying to order by?

[PeerFly]

Now i'm confused... what are you trying to order by?

 

I'm trying to order by the value of the offer from the offers table UNLESS there is an entry in the exceptions table with that offerid. If there is an exception it will be lower or higher than the default value (because it's an exception to the default value) so it needs to correctly sort with the other values.

[DeanWhitehouse]

try

 

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

[fenway]

try

 

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

 

Actually, I think you mean:

 

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

[PeerFly]

When I try that code or blades code both result in no rows returned.

[PeerFly]

WOW! I finally got it resolved!

 

SELECT o.offerid

    , o.name

    , o.incent

    , o.type

    , o.reporting

    , o.category

    , COALESCE(e.pay_value,o.pay_value) AS pay_value

  FROM offers AS o

LEFT OUTER

  JOIN exceptions AS e

    ON e.offerid = o.offer_id

WHERE o.status = 'On'

ORDER

    BY pay_value

[fenway]

Interesting... COALESCE() simply returns the first NON-NULL value... but IFNULL() should have always given you a NON-NULL value... werid.