创建一个MYSQL Select语句，其中包含购物篮和产品表中的信息

I've been trying to create a shopping basket class and have hit a brick wall when trying to output by shopping basket contents.

我一直在努力创建一个购物篮课程,并在尝试按购物篮内容输出时碰到了一堵砖墙。

My users basket information is stored in a table with the following structure.

我的用户购物篮信息存储在具有以下结构的表中。

     CREATE TABLE IF NOT EXISTS basket (
            `ID` int PRIMARY KEY AUTO_INCREMENT,
            `ProductID` int,
            `Quantity` int,
            `OptionID` int,
            `Cookie` varchar(40)
            ;

Where the Cookie field is a unique identifier for the user stored in a cookie.

Cookie字段是存储在cookie中的用户的唯一标识符。

My products tables is structured as follows:

我的产品表格结构如下:

    CREATE TABLE IF NOT EXISTS products (
            ID int PRIMARY KEY AUTO_INCREMENT,
            Title varchar(200),
            Description text,
            Specification text,
            Price decimal(10,2),
            CategoryID int,
            Weight int,
            Options text,
            OptionValues text,
            OptionCost text,
            Discount int(2),
            Featured boolean,
            Images text,
            Stock int,
            Related text,
            Offer boolean,
            OfferDetails boolean,
            Language int
            ;

What I want to do SELECT the items from my basket with their respective product Title and price information from the products table.

我想做什么从我的购物篮中选择各自的产品标题和产品表中的价格信息。

So in essence I need a sql statement that will output the following using the productID to link the two tables:

所以本质上我需要一个sql语句,它将使用productID输出以下内容来链接这两个表:

ProductID From basket table

ProductID来自购物篮桌

Quantity From basket table

数量来自篮子表

Title From products table

标题来自产品表

Description From products table

描述从产品表

Price From products table

价格来自产品表

I have done a similar thing before by looping through the basket array and then querying the products table with the current ProductID but surely this can be done with one query?

我之前做过类似的事情,循环遍历篮子数组,然后用当前的ProductID查询products表,但肯定可以用一个查询完成吗?

2 个解决方案

#1

Try this:

SELECT
  b.ProductID,
  b.Quantity,
  p.Title,
  p.Description,
  p.price
From basket b
INNER JOIN products p ON b.ProductID = p.Id;

Please read these:

请阅读以下内容:

JOIN(SQL).
Visual Representation of SQL Joins.

SQL连接的可视化表示。
A Visual Explanation of SQL Joins.

SQL连接的可视化解释。

#2

SELECT b.ProductID, b.Quantity, p.Title, p.Description, p.Price 
FROM basket b 
     LEFT JOIN products p ON (b.ProductID = p.ID);

Also have in mind this:

还要记住这个:

An INNER JOIN excludes from the results set any records that do not exist in both tables.

INNER JOIN从结果集中排除两个表中不存在的任何记录。

A LEFT JOIN includes all records from the first table and all matching records from the second table, assuming the query does not exclude null values from the second table.

LEFT JOIN包括来自第一个表的所有记录和来自第二个表的所有匹配记录,假设查询不排除第二个表中的空值。

#1