如何使用内部联接查询从mysql数据库表中获取单行？

I have following 2 tables in mysql database :

我在mysql数据库中有以下2个表:

1) products

p_id      p_title   p_price   u_id  
----------------------------------
1         tile      123       25
2         tile      133       24
3         tile      143       25

2) product_images

id       img_name   p_id      u_id   
1        name       1         25
2        name       1         25
3        name       2         24

I want to get all row from products table and only one row from product_images table. So to get this I am using following sql query but it's return all rows from product_images table. Should be one !

我想从product表中获取所有行,并且只从product_images表中获取一行。所以为了得到这个,我使用以下sql查询,但它返回product_images表中的所有行。应该是一个!

My Query :

我的查询:

$get_menu = mysqli_query($conn, 
"SELECT DISTINCT products.p_title, products.p_price, 
product_images.p_list_image, chef_profile.live_at, chef_profile.fname,
chef_profile.lname FROM products LEFT JOIN product_images ON products.p_id
= product_images.p_id LEFT JOIN chef_profile ON products.u_id = 
chef_profile.u_id GROUP BY product_images.p_list_image") or die('sorry');

I am using this query in php while loop so that I want to show all unique data from products table and only one image from product_images table.

我在php循环中使用此查询,以便我想显示products表中的所有唯一数据,并且只显示product_images表中的一个图像。

$num_menu =  mysqli_num_rows($get_menu);
    while( $get_result = mysqli_fetch_array($get_menu) ) {
        /*echo '<pre>';
        print_r($get_result);*/
        $menu_title = htmlspecialchars($get_result['p_title']);
        $menu_price = htmlspecialchars($get_result['p_price']);        
        $menu_image = htmlspecialchars($get_result['p_list_image']) ? htmlspecialchars($get_result['p_list_image']) : "no-menu-preview.png";
        $live_at =  htmlspecialchars($get_result['live_at']);
        $fname = htmlspecialchars($get_result['fname']);
        $lname = htmlspecialchars($get_result['lname']);

        // echo..........data.. her...
    }

2 个解决方案

#1

There you have it.

你有它。

You only have to group by p_id to get 1 result for each product.

您只需按p_id分组即可获得每个产品的1个结果。

SELECT DISTINCT products.p_title, products.p_price, product_images.p_list_image, chef_profile.live_at, chef_profile.fname, chef_profile.lname
FROM products
LEFT JOIN product_images ON products.p_id = product_images.p_id
LEFT JOIN chef_profile ON products.u_id = chef_profile.u_id
GROUP BY products.p_id

Hope it helps ;)

希望能帮助到你 ;)

#2

SELECT products.p_title, 
products.p_price, 
product_images.p_list_image, 
chef_profile.live_at, 
chef_profile.fname,
chef_profile.lname 

FROM products 
LEFT JOIN product_images ON products.p_id = product_images.p_id 
LEFT JOIN chef_profile ON products.u_id = chef_profile.u_id 

GROUP BY products.p_id,product_images.p_list_image

#1