Hi.

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:

Table A:

This lists information of a company_ID and a number of orders placed by that company for a certain item as follows:

hya_ID, company_ID

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.

It is:

company_ID, company_name.

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.

Thanks.

Sounds like you will need to do a JOIN, but without some more data on your table structures can't specify exactly what. Show us the results of:
for all the tables involved.

I have now solved it as follows.


I do have another question however.

I have table_A that has the countries of a company, these can be countries where several offices are located, they are linked by the company_ID.

So table A contains country 1, country 2, country 3, and so on all linked via the company_ID.

table B contains the details of the main HQ address of the company.

We want to display, all companies, in a row, with their full HQ address from table B, and in the same single row, list all the countries associated with that company in table A.

So you end up with:

company name - company tel - company fax and then a list of country 1, country 2, country 3 in one box without repeating the contry name, tel and fax etc...

Can you help at all?

Thanks.

Arwyn.

Many thanks.

Arwyn.