Leaderboard
Popular Content
Showing content with the highest reputation on 12/11/2019 in all areas
-
Make your join a LEFT JOIN and add the job id condition as part of the join. SELECT menu_name, menu_price, menu_item_name, menu_item_category, b.menu_item_id, a.menu_id, d.job_id, d.menu_item_qty FROM ssm_menu a INNER JOIN ssm_menu_connection b ON a.menu_id = b.menu_id INNER JOIN ssm_menu_items c ON b.menu_item_id = c.menu_item_id LEFT JOIN ssm_menu_order d ON c.menu_item_id = d.menu_item_id AND d.job_id=27 WHERE a.menu_id = 1 On a side note, consider re-formatting your queries. Your current style is quite difficult to read, something like what I put above is much easier.1 point
-
Perhaps something without all those non-breaking spaces <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta http-equiv="Lang" content="en"> <title>Example</title> <style type="text/css"> table { width: 80%; margin: 20px auto; font-family: calibri, sans-serif;} caption { border-bottom: 1px solid gray; } th { text-align: left; padding: 8px 2px; color: #999;} td { color: #25b0e9; padding: 8px 4px; } </style> </head> <body> <table> <caption>Channel State</caption> <tr> <th> </th> <th>C1</th> <th>C2</th> <th>C3</th> <th>C4</th> <th>C5</th> <th>C6</th> <th>C7</th> <th>C8</th> </tr> <tr> <th>Left (+)</th> <td>✓</td> <td>✓</td> <td>✓</td> <td> </td> <td>✓</td> <td> </td> <td> </td> <td> </td> </tr> <tr> <th>Left (-)</th> <td> </td> <td> </td> <td>✓</td> <td>✓</td> <td> </td> <td> </td> <td> </td> <td> </td> </tr> </table> </body> </html>
1 point -
As you test data contained only a menu that was a perfect match to the test order and a menu that contained everything I added a couple of extra menus and orders. SELECT m.menu_id , m.menu_name , COUNT(*) as matched , ROUND(count(*)/menuitems*100, 1) as `%menu` , ROUND(count(*)/orderitems*100, 1) as `%order` FROM ssm_menu m INNER JOIN ssm_menu_connection c ON c.menu_id = m.menu_id INNER JOIN ssm_menu_order o ON o.menu_item_id = c.menu_item_id INNER JOIN ( SELECT job_id , COUNT(DISTINCT menu_item_id) as orderitems FROM ssm_menu_order GROUP BY job_id ) jtot ON jtot.job_id = o.job_id INNER JOIN ( SELECT menu_id , COUNT(DISTINCT menu_item_id) as menuitems FROM ssm_menu_connection GROUP BY menu_id ) mtot ON m.menu_id = mtot.menu_id WHERE o.job_id = 27 GROUP BY m.menu_id ORDER BY matched DESC, `%menu` DESC, `%order` DESC; +---------+----------------------------------------------------------+---------+-------+--------+ | menu_id | menu_name | matched | %menu | %order | +---------+----------------------------------------------------------+---------+-------+--------+ | 1 | Menu One | 3 | 100.0 | 100.0 | | 2 | Private Dinner Party/Wedding - 3 Course Fine Dining Menu | 3 | 18.8 | 100.0 | | 3 | Menu Three | 2 | 66.7 | 66.7 | | 4 | Menu Four | 2 | 50.0 | 66.7 | | 5 | Menu Five | 1 | 25.0 | 33.3 | +---------+----------------------------------------------------------+---------+-------+--------+1 point
This leaderboard is set to New York/GMT-04:00