2

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

Community
  • 1
  • 1
Dante Pereyra
  • 129
  • 1
  • 1
  • 7
  • 1
    I suggest you re-read the text book. I'm betting this is a homework assignment. – kirbs Nov 06 '12 at 01:03
  • Sorry if it seems too easy or straight-forward. It's one of my first jobs working directly with SQL databases. I don't even know where to begin or what to search for in order to get assistance with this. – Dante Pereyra Nov 06 '12 at 01:32
  • This is my best attempt: http://pastebin.com/0j2vnVEb but I bet there is a shorter and more efficient way of doing this. I would really love your feedback on this. – Dante Pereyra Nov 06 '12 at 02:07

3 Answers3

2

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.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • Thanks ElectricLlama. The tip about normalizing a table is really usefull. This is actually a work in progress so I might be able to modify the way tables are saved. I'll research on that! ** BTW: 30 products is the limit for the order form. ** // Also, I appreciate everyone else's attempt to make me learn MySQL but I'M ACTUALLY DOING IT RIGHT NOW. A tip like E-Llama's are a lot more helpful. – Dante Pereyra Nov 06 '12 at 12:24
  • It's AMAZING how easy the queries were when the table was normalized!! I was born again. THANKS! – Dante Pereyra Nov 06 '12 at 15:05
  • Excellent! I'm glad you are in the development process early enough to make this change, often people have to work with systems that cannot be changed. – Nick.Mc Nov 07 '12 at 02:47
0

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

Alexander
  • 2,320
  • 2
  • 25
  • 33
0

I think that your table should be look like

Table orders

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

orderno
client
code
cant
note

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

Mitesh
  • 477
  • 1
  • 6
  • 22