Member Avatar for apbdan

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
  • 106 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by apbdan
Member Avatar for trudge

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.

Member Avatar for apbdan

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.