没有重复值的MySQL连接查询

Question

1. 发票表

 SELECT id, fname, gtotal, `date` FROM invoice WHERE id = 1;


| id |   fname | gtotal |                  date |
|----|---------|--------|-----------------------|
|  1 | Brandon |    860 | May, 11 2016 00:00:00 |

1. invoice_contents 表，

SELECT * FROM invoice_contents WHERE invoice_id = 1;


| id | invoice_id |       item | price | quantity | discount | total |
|----|------------|------------|-------|----------|----------|-------|
|  1 |          1 |   Dextrose |    10 |       10 |        5 |    95 |
|  2 |          1 |   Nescaine |    20 |       30 |       10 |   540 |
|  3 |          1 | Anticavity |    30 |       10 |       25 |   225 |

1. 这个 JOIN 查询

SELECT invoice.id, invoice.fname, invoice_contents.item,
       invoice_contents.price, invoice_contents.quantit,
       invoice_contents.discount, invoice_contents.total, 
       invoice.gtotal
  FROM invoice_contents
 INNER JOIN invoice ON invoice_contents.invoice_id=1 AND invoice.id=1;

给出这个结果。

 | id |   fname |       item | price | quantity | discount | total | gtotal |
 |----|---------|------------|-------|----------|----------|-------|--------|
 |  1 | Brandon |   Dextrose |    10 |       10 |        5 |    95 |    860 |
 |  1 | Brandon |   Nescaine |    20 |       30 |       10 |   540 |    860 |
 |  1 | Brandon | Anticavity |    30 |       10 |       25 |   225 |    860 |

我需要这个结果。

| id |   fname |       item | price | quantity | discount | total | gtotal |
|----|---------|------------|-------|----------|----------|-------|--------|
|  1 | Brandon |   Dextrose |    10 |       10 |        5 |    95 |    860 |
|    |         |   Nescaine |    20 |       30 |       10 |   540 |        |
|    |         | Anticavity |    30 |       10 |       25 |   225 |        |

我只是 MySQL 的初学者。从今天早上开始，我一直在尝试通过尝试不同的组合来获得这种输出，请帮助我。

Answer 1

@Rex，您的选择是正确的。您应该使用一些脚本制作所需的输出，例如PHP.