SELECT
plan.*, trkg.curwipprice_online, trkg.addr
FROM
plan
LEFT
JOIN
trkg
ON
plan.cliorder = trkg.cliorder
AND trkg.addr = '$addr_clear'
WHERE
(TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end) )
ORDER BY ((1-(trkg.curwipprice_online/plan.edprice))*100) DESC
SELECT ...
FROM plan
LEFT OUTER
JOIN ( SELECT cliorder
, MIN(curwipprice_online) AS lowest
FROM trkg
WHERE addr = '$addr_clear'
GROUP
BY cliorder ) AS x
ON x.cliorder = plan.cliorder
LEFT OUTER
JOIN trkg
ON trkg.cliorder = x.cliorder
AND trkg.curwipprice_online = x.lowest
AND trkg.addr = '$addr_clear'
I’m following you, but I created two unintended consequences in my OP.
I didn’t state in my OP that I also need the unmatched records from the table called plan. Turns out that deleting the second LEFT JOIN resolved that issue. I also changed “MIN(curwipprice_online) AS lowest” to “MIN(curwipprice_online) AS curwipprice_online” in an attempt to match the output format in the OP. Here’s the current query:
SELECT
*
FROM
plan
LEFT OUTER JOIN
(SELECT
cliorder, MIN(curwipprice_online) AS curwipprice_online
FROM
trkg
WHERE
addr = '$addr_clear'
GROUP
BY cliorder
) AS x
ON
x.cliorder = plan.cliorder
The other unintended consequence is that I need each row to produce an array that ends with these keys (see my OP):
[68]=> string(4) “0.47” [“curwipprice_online”]=> string(4) “0.47”
[69]=> string(14) “1840 Pawnee St” [“addr”]=> string(14) “1840 Pawnee St”
The current query selects rows that produce an array that ends with:
[68]=> NULL
[69]=> NULL [“curwipprice_online”]=> NULL }
I don’t think the order matters as much as that missing key.
How do I get the [“addr”] key from the table called trkg, into the selection results? I tried adding “, addr” in the second select, but nogo.
When I do that I loose the item_names on the unmatched plan rows.
To confirm, I need all the columns from all the rows from plan that qualify on the WHERE in the OP plus the curwipprice_online and addr columns appended at the end of the plan columns on the plan rows that match the trkg rows WHERE addr = ‘$addr_clear’, keeping only the rows with the lowest trkg.curwipprice_online when matches produce multiple item names.
Where does the WHERE in the OP fit into your #4 post?
if item_names is in the plan table, and the plan table is the left table in a LEFT OUTER JOIN, then you will always get it on all returned rows, matched and unmatched
There is no “between the FROM clause and the ORDER BY clause”
So, I did this (because it made the most sense to me) , but it threw this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘LEFT OUTER JOIN ( SELECT cliorder , MIN(curwipprice_online) A’ at line 6
Here’s the script:
SELECT *
FROM plan
WHERE
(TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end) )
LEFT OUTER
JOIN ( SELECT cliorder
, MIN(curwipprice_online) AS lowest
FROM trkg
WHERE addr = '$addr_clear'
GROUP
BY cliorder ) AS x
ON x.cliorder = plan.cliorder
LEFT OUTER
JOIN trkg
ON trkg.cliorder = x.cliorder
AND trkg.curwipprice_online = x.lowest
AND trkg.addr = '$addr_clear'
I reversed the join, used your help, and a lot of trial and error. Voila!
Thank-you very much r937.
SELECT
plan.*, min(trkg.curwipprice_online) AS curwipprice_online, trkg.addr
FROM
trkg
RIGHT OUTER JOIN
plan
ON
plan.cliorder = trkg.cliorder
AND
trkg.addr = '$addr_clear'
WHERE
TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end) )
GROUP BY
plan.item_name;