CODEWITHSUNDEEP
mysqldatejoinselectsubquery
Ubuntu Tricks
Ubuntu Tricks

Reputation: 67

how to print data from second table in MySQL

I have main table called companyNames, and each row contains the name of the company, for eg. TCS, TATAMOTORS, INFY, etc. and also having their tables, like table TCS, table INFY etc, so i'm trying to print all information from these table. but it is not printing..

 +------------+
| names      |
+------------+
| TCS        |
| INFY       |
| TATAMOTORS |
| TATASTEEL  |
+------------+
Company Name

+------------+---------+---------+---------+--------+---------+-----------+
| date       | high    | low     | open    | close  | volume  | adj_close |
+------------+---------+---------+---------+--------+---------+-----------+
| 2015-06-15 | 1260.22 |  1240.5 | 1252.65 | 1252.4 | 2525176 |   1098.59 |
| 2015-06-16 | 1259.65 | 1247.22 |    1250 |   1256 | 1267396 |   1101.74 |
| 2015-06-17 | 1264.57 |  1250.9 |  1260.5 | 1256.9 | 1975720 |   1102.53 |
+------------+---------+---------+---------+--------+---------+-----------+  
                               TCS

+------------+---------+---------+--------+--------+---------+-----------+
| date       | high    | low     | open   | close  | volume  | adj_close |
+------------+---------+---------+--------+--------+---------+-----------+
| 2018-07-15 |  720.22 |   120.5 |   1255 | 1252.4 | 2525176 |   1098.59 |
| 2017-03-11 | 1259.65 | 1247.22 |   12.5 |   1256 |    1267 |   1101.74 |
| 2014-01-14 | 1264.57 |  1250.9 | 1260.5 | 1256.9 | 1975720 |   1102.53 |
+------------+---------+---------+--------+--------+---------+-----------+ 
                                 INFY

after 

select * from (select names from companyNames) as a;

above query is just printing the names of the companies not the information stored in TCS, INFY and other tables.

+------------+
| names      |
+------------+
| TCS        |
| INFY       |
| TATAMOTORS |
| TATASTEEL  |
+------------+

please help me to print all companies information, also give me suggestions to make the database more effiecient.

Upvotes: 1

Views: 185

Answers (1)

GMB
GMB

Reputation: 222542

help me to print all companies information

Your current schema makes it very hard to achieve what otherwise seems like a simple task. You would need to either hardcode the company names, or use dynamic SQL. The first solution would look like:

select c.*, coalesce(t.high, i.high) high, coalesce(t.low, i.low) low, ...
from companies c
left join tcs  t on c.name = 'TCS'
left join infy i on c.name = 'INFY'
left join ...

and also give me suggestions to make the database more effiecient

Do not use separate tables to store the data of different companies. You should have just one table, with a column that refers to the primary key of the companies table:

Table companies:

company_id    company_name
1             TCS
2             INFY

Table stocks: 

company_id  date          high       low    ...
1           2015-06-15    1260.22    1240.5
1           2015-06-16    1259.65    1247.22
2           2015-06-15     720.22     120.5
2           2015-06-16    1259.65    1247.22

Then, you can write your query as a simple join, regardless how actual company names:

select c.company_name, s.*
from companies c
inner join stocks s on s.company_id = c.company_id

Upvotes: 1

Related Questions