I am fairly new to mysql, so help is appreciated.
I have two tables and I wish to count some results from two tables and combine them onto one row, and then join an ID to another table.
The problem is as follows:
This lists information of a company_ID and a number of orders placed by that company for a certain item as follows:
The following code produces the result I require:
SELECT hya_ID, company_ID, count(*) AS TOTAL from table_name_A GROUP BY company_ID
I then get a result of say:
hya_ID Company_ID total
93 45 2
95 56 4
93 78 8
and so on.
I then have table B:
This has the cost of sales as follows.
hya_ID, size_value, order despatched
I use the following and get the total sales for each item.
SELECT hya_ID, size_value, COUNT(*) AS TOTAL FROM table_name_B GROUP BY size_value WHERE order_despatched_ID=3
I get a result as follows:
hya_ID size_value order_despatched
93 950 1
95 780 0
93 850 1
I then want to show these results, by the common key of hya_ID, in a line, showing the total
number of hya_ID by a company and the total size_value relating to hya_ID in table B and then link to a third table that gives the actual name of the company_ID, and this is table C.
So we end up with something like the following.
company_name hya_ID (total) size_value (total)
ABC company 3 340
XYZ company 2 550
and so on and so forth.
Has anyone any ideas how to achieve this.