I have this example tables:

我有这个示例表:

table ORDERS

client   orderno   cant1   code1   notes1   cant2   code2   notes2   cant[i]   code[i] [...]
--------------------------------------------------------------------------------------
1          1        3      AA01    Test      4      BB01    Testing
2          2        10     XX05    Test      



table PRODUCTS

code   prod    price
---------------------
AA01   Engine   100  
BB01   Wheel     50  



table CLIENTS

client   name      address     telephone
-----------------------------------------
  1     Maxwell   24 1st st    0987654321
  2     Hammer    77 main st   1234567890

I need to relate them to get the quantity, name of the product and price for each of the product lines (they are 30 cant[i], code[i] and notes[i]) and the customer's information (name, address, etc)

我需要将它们联系起来,以获取每个产品系列的数量,产品名称和价格(它们是30个[i],代码[i]和注释[i])和客户的信息(名称,地址,等等)

I found this case, but I don't understand how to apply it to mine: SQL query two tables with relation one-to-many

我发现了这种情况,但我不明白如何将它应用于我的:SQL查询两个关系一对多的表

I hope it's not too complex.

我希望它不是太复杂。

Thanks in advance!

提前致谢!

EDIT

Thanks to ElectricLlama I realized the problem here is the table where the order is storaged. According to his answer, the normalization of the database would improve the way I'm able to get the info.

感谢ElectricLlama,我意识到这里的问题是存储订单的表。根据他的回答,数据库的规范化将改善我获取信息的方式。

For anyone interested in this solution, I found this great website: http://www.devshed.com/c/a/MySQL/An-Introduction-to-Database-Normalization/

对于对这个解决方案感兴趣的人,我找到了这个很棒的网站:http://www.devshed.com/c/a/MySQL/An-Introduction-to-Database-Normalization/

This SO answer clears it ALL! Super clear and understandable! https://*.com/a/1258776/888292

这个SO答案清除了所有!超级清晰易懂! https://*.com/a/1258776/888292

3 个解决方案

#1

Looking at what's in your link - yes it seems like a lot of nonsense, but it is probably the only way to get what you want.

看看你链接中的内容 - 是的,它似乎有很多废话,但它可能是获得你想要的唯一方法。

The problem is that your table is not normalised. Specifically you should not have fields called code1 code2 code3 code4... code30

问题是您的表没有规范化。具体来说,你不应该有名为code1 code2 code3 code4 ... code30的字段

There are many flaws with this design including what happens when a client has 31 products?

这种设计存在许多缺陷,包括当客户有31种产品时会发生什么?

In a normalised database you would have a table with one set of cant, code and notes, and you would have one row per product.

在规范化的数据库中,您将拥有一个包含一组cant,code和notes的表,每个产品只有一行。

But I guess you are not in a position to normalise it.

但我猜你不能正常化它。

So well done for coming up with your own answer, and you now you also have first hand experience of the repercussions of not normalising a database.

为您提供自己的答案做得非常好,现在您还可以亲身体验不规范化数据库的影响。

What you might want to consider is creating a view that will normalise this for you. It will introduce performance issues but it will give you an introduction to views, and give you an opportunity to see how the solution would look like against a normalised table.

您可能需要考虑的是创建一个视图,为您规范化。它将介绍性能问题,但它将为您提供视图介绍,并让您有机会了解解决方案对规范化表的看法。

#2

Ditto @ElectricLlama & here are a few links that should help you learn SQL:

同上@ElectricLlama&这里有一些链接可以帮助你学习SQL:

W3Schools: SQL Tutorial

W3Schools:SQL教程

SQLServerCentral.com Stairway Series

SQLServerCentral.com Stairway系列

#3

I think that your table should be look like

我认为你的桌子应该是这样的

Table orders

-------------

orderno
client
code
cant
note

orderno客户端代码不能注意

Here make orderno, client and code make a composite primary key of the table

这里使orderno,client和code成为表的复合主键

#1