Company (Company Name (PK), Address, Representative, Product) Innovative companies (name of company (PK,FK), selection year, reason for selection) Excellent company (Company Name (PK,FK), Selection Year, Reason for Selection) Advanced companies (Company Name (PK,FK), Selection Year, Reason for Selection)
ERD is a bit sloppy, but... In situations where there is a table as shown above,
I'd like to make another table
The name of the company is written on the table There is a column of numbers about how many companies were selected among advanced, innovative, and excellent.
For example... It's a table like the number of outstanding companies (company name (PK), how many of the best innovators (int))
Currently, the table in the picture above contains all the data. In that state, I want to make a new table as above, send an SQL query, and put the company name and number at once. What should I do?
I don't know if you understand my question.
Or if there's something I'm not sure about, please point it out Thank you.
database
First of all, from a basic database management perspective, it would be better to use a view (VIEW) rather than a new table because you need to avoid data duplication and maintain consistency.
Note: http://www.w3schools.com/sql/sql_view.asp
First, you can use GROUP BY to find the number of times that a company has been selected as advanced company as follows. Other top companies and innovators can also be obtained in the same way.
SELECT Company Name, COUNT (Selected Year) AS Advanced Selection Number FROM Advanced Company GROUP BY Company Name
If you join the above results with the Company table, you can get the result you want, and you can make VIEWr and use it. If you have more data and slow down, you should tune SQL more, or find another way.
Here is an example of a simple SQL in which companies query the number of advanced and best companies at once. Please keep in mind.
SELECT Enterprise.Company name, advanced.the number of selectionsNumber of selections FROM companies
LEFT JOIN
(SELECT Company Name, COUNT (Selected Year) AS Selection Number of FROM Advanced Company GROUP BY Company Name) Advanced
an ON companyCompany Name = Advanced.Company name
LEFT JOIN
(SELECT Company Name, COUNT (Selected Year) AS Selection Number of FROM Excellent Company GROUP BY Company Name) Excellent
an ON companyCompany Name = Excellent.Company name
To use the top like a table, you can create and use VIEW.
CREATE VIEW Company Information AS SELECT...; //(SELECT...) is the previous SELECT statement)
© 2024 OneMinuteCode. All rights reserved.