0

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.

2
Contributors
2
Replies
3
Views
9 Years
Discussion Span
Last Post by apbdan
0

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:

describe table_name

for all the tables involved.

0

I have now solved it as follows.

SELECT table_A.company_ID AS company, COUNT( * ) AS total, SUM(size_value) AS size, table_B.qbook_ID, table C.company_ID, table_C.company_name						FROM table_A							INNER JOIN table_B ON table_A.hy_ID=table_B.hya_ID				INNER JOIN table_C ON table_B.company_ID = table_C.company_ID
WHERE table_A.despatched_ID=3 AND table_B.nonusd_hy_qbook_ID=1
GROUP BY table_B.company_ID				ORDER BY total DESC

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.