SQL query - Two tables with relation many to one (Normalization issue)

Question

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)

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

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.

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

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

Answer 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

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

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.

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.