MySQL联接-通过另一个表PHP排序结果 [英] MySQL join - ordering results via another table PHP

问题描述

我有2个MySQL表，其中有一个数字列来组织我需要显示的项目的顺序:

item_names

menu_id  |  dish_id  |  section_id  |  item_name
--------------------------------------------------
1        | 23        |      2       |   Pie       
1        | 24        |      2       |  Fish       
1        | 25        |      3       |  Apples     
1        | 26        |      2       |  Onions     
1        | 27        |      2       |  Chips

link_extras

extra_id |  dish_id  | sort  
-----------------------------
1        | 23        | 2     
2        | 23        | 2     
3        | 23        | 2      
1        | 24        | 0     
5        | 24        | 0     
6        | 26        | 3     
12       | 26        | 3     
1        | 27        | 1  
1        | 25        | 0    

基本上，我想做的是从表item_names中提取具有特定menu_id和section_id的每道菜，并根据link_extras表中的sort列对输出进行排序. /p>

到目前为止:

$query="SELECT a.item_name, a.dish_id, b.sort
    FROM item_names AS a, link_extras AS b 
       WHERE a.menu_id='1'
           AND a.section_id='2'
           AND b.dish_id=a.dish_id
       GROUP BY b.dish_id
       ORDER BY b.sort";

我对数据库很陌生，因此不胜感激.我追求的结果是

Fish
Chips
Pie
Onions

不幸的是，无法正确获得订单.

解决方案

您需要使用简单的JOIN

SELECT a.item_name, a.dish_id, b.sort
    FROM item_names AS a 
    JOIN link_extras AS b 
      ON a.dish_id = b.dish_id
   WHERE menu_id = 1
    AND section_id = 2
       GROUP BY b.dish_id
ORDER BY b.sort

输出:

| ITEM_NAME | DISH_ID | SORT |
------------------------------
|      Fish |      24 |    0 |
|     Chips |      27 |    1 |
|       Pie |      23 |    2 |
|    Onions |      26 |    3 |

查看此SQLFiddle

I have 2 MySQL tables, one of which has a numeric column to orgainise the order I need the items to be displayed:

item_names

menu_id  |  dish_id  |  section_id  |  item_name
--------------------------------------------------
1        | 23        |      2       |   Pie       
1        | 24        |      2       |  Fish       
1        | 25        |      3       |  Apples     
1        | 26        |      2       |  Onions     
1        | 27        |      2       |  Chips

link_extras

extra_id |  dish_id  | sort  
-----------------------------
1        | 23        | 2     
2        | 23        | 2     
3        | 23        | 2      
1        | 24        | 0     
5        | 24        | 0     
6        | 26        | 3     
12       | 26        | 3     
1        | 27        | 1  
1        | 25        | 0    

Basically what I am trying to do is extract each dish with a certain menu_id and section_id from the table item_names and order the output in respect to the sort column in the link_extras table.

so far:

$query="SELECT a.item_name, a.dish_id, b.sort
    FROM item_names AS a, link_extras AS b 
       WHERE a.menu_id='1'
           AND a.section_id='2'
           AND b.dish_id=a.dish_id
       GROUP BY b.dish_id
       ORDER BY b.sort";

I am quite new to databases so would appreciate any help. The result I am after is

Fish
Chips
Pie
Onions

Unfortunately just can't get the order correct.

解决方案

You need to use a simple JOIN

SELECT a.item_name, a.dish_id, b.sort
    FROM item_names AS a 
    JOIN link_extras AS b 
      ON a.dish_id = b.dish_id
   WHERE menu_id = 1
    AND section_id = 2
       GROUP BY b.dish_id
ORDER BY b.sort

Output:

| ITEM_NAME | DISH_ID | SORT |
------------------------------
|      Fish |      24 |    0 |
|     Chips |      27 |    1 |
|       Pie |      23 |    2 |
|    Onions |      26 |    3 |

See this SQLFiddle

这篇关于MySQL联接-通过另一个表PHP排序结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！