mysql - SQL Fiddle Complex不显示任何内容

http://sqlfiddle.com/#!9/73ad5f/41

CREATE TABLE customers (
  customer_id     INT(10) NOT NULL AUTO_INCREMENT,
  first_name      VARCHAR(20) NOT NULL,
  last_name       VARCHAR(20) NOT NULL,
  street_address  VARCHAR(30),
  apt_number      INT(5),
  city            CHAR(30),
  state           CHAR(2),
  zip_code        INT(5),
  home_phone      INT(10),
  mobile_phone    INT(10),
  other_phone     INT(10),
  PRIMARY KEY (customer_id)
);
INSERT INTO customers(first_name, last_name, street_address, apt_number, city, state, zip_code,
home_phone, mobile_phone, other_phone)
VALUES("Z","S","Lovely Roses",911,"Hills","CA",90210, NULL, NULL, NULL);

CREATE TABLE donuts (
  donut_id      INT(10) NOT NULL AUTO_INCREMENT,
  name          VARCHAR(30),
  description   VARCHAR(50),
  unit_price    DECIMAL(8,3),
  PRIMARY KEY (donut_id)
);

INSERT INTO donuts (donut_id,name,description,unit_price)
VALUES (1,"Maple Bar","Delicious Donut covered with maple creamy icing",1.99);

CREATE TABLE salesorders (
  donut_order_id        INT(10) NOT NULL AUTO_INCREMENT,
  customer_id           INT,
  date                  DATETIME,
  special_handling_note TEXT,
  PRIMARY KEY (donut_order_id),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO salesorders ( donut_order_id,date,special_handling_note )
VALUES( 0000001,"2018-04-22","Please include plates and napkins");

CREATE TABLE salesorderslineitem (
  donut_order_id      INT(10) NOT NULL,
  donut_id            INT(10) NOT NULL,
  qty                 INT(10) NOT NULL,
  PRIMARY KEY (donut_order_id, donut_id),
  FOREIGN KEY (donut_order_id)
  REFERENCES salesorders(donut_order_id),
  FOREIGN KEY (donut_id) REFERENCES donuts(donut_id)
);

INSERT INTO salesorderslineitem (donut_order_id,donut_id, qty)
VALUES (0000001,1, 12);

CREATE VIEW AllCusomters AS
SELECT
    CONCAT(first_name, CHAR(32), last_name) AS FullName,
    street_address,
    apt_number,
    city,
    state,
    zip_code,
    home_phone,
    mobile_phone,
    other_phone

FROM customers;

CREATE INDEX DonutNameField ON donuts(name);


HERE's my QUERY where I am not getting anything to display.

SELECT
    first_name,
    last_name,
    street_address,
    apt_number,
    city,
    state,
    zip_code,
    home_phone,
    mobile_phone,
    other_phone,
    name,
    description,
    unit_price,
    date,
    qty,
    special_handling_note,
    salesorderslineitem.donut_order_id,
    donuts.donut_id,
    customers.customer_id

    FROM customers,donuts,salesorders,salesorderslineitem

    WHERE salesorders.customer_id = customers.customer_id
    AND salesorderslineitem.donut_order_id = salesorders.donut_order_id
    AND salesorderslineitem.donut_id = donuts.donut_id

    ORDER BY salesorders.donut_order_id;

最佳答案

如评论中所述，您没有匹配的客户。您输入salesorders的行的customerid为空，因为您没有设置值。

INSERT INTO salesorders ( donut_order_id,date,special_handling_note )
VALUES( 0000001,"2018-04-22","Please include plates and napkins");

我改成这个了

INSERT INTO salesorders ( donut_order_id, customer_id,date,special_handling_note )
VALUES( 0000001,1,"2018-04-22","Please include plates and napkins");

FIDDLE
您还应该考虑使用正确的连接语法，因为这样更容易理解。

FROM customers
INNER JOIN salesorders on salesorders.customer_id = customers.customer_id
INNER JOIN salesorderslineitem on salesorderslineitem.donut_order_id = salesorders.donut_order__id
INNER JOIN donuts on donuts.donut_id = salesorderslineitem.donut_id

关于mysql - SQL Fiddle Complex不显示任何内容，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50047747/