By Shailaja Gupta Kapoor


2020-06-30 03:19:07 8 Comments

Say you have many tables with the names as Company1, Company2 etc. till Company 1000. Structure as given below:

Company 1
product cost($)
A        C1A
B        C1B
C        C1C

There are 1000 such tables . All cpmapnies sell 3 products A , B,C . How will you compare your company say Company 520 with all the other 1000 companies.

All these tables are under database Companies

How to join all these tables in SQL simultaneously?

I very well know about joins, but I know that at one time we can only join two tables. Is there a way to join multiple tables together in SQL

Expected output

product cost($)_Company1  cost($)_Company2   cost($)_Company3   
A        C1A                     C2A          C3A
B        C1B                     C2B          C3B
C        C1C                     C2C          C3C  and so on for all 1000 companies

Note in combined expected output, _Company name is appended to cost column names to identify the company corresponding to cost

I was asked this in interview, how you will do it at one go, and I didnt know the answer. Can anyone please help. Structures of tables as in names of columns in all tables is same product and cost($) being the column names

2 comments

@mkRabbani 2020-06-30 03:26:42

Try this below example. There is no limitation of joining tables.

SELECT Company1.Product,
Company1.cost($) 'cost($)_Company1',
Company2.cost($) 'cost($)_Company2',
Company3.cost($) 'cost($)_Company3',
CompanyN.cost($) 'cost($)_CompanyN'
FROM Company1
INNER JOIN Company2 ON Company1.Product = Company2.Product
INNER JOIN Company3 ON Company1.Product = Company3.Product
INNER JOIN CompanyN ON Company1.Product = CompanyN.Product

@zealous 2020-06-30 03:27:52

You can try the following using simple join

 select
    t1.product,
    t1.cost as 'cost($)_Company1',
    t2.cost as 'cost($)_Company2',
    t3.cost as 'cost($)_Company3',
from company1 t1

join company2 t2
on t1.product = t2.product

join company3 t3
on t2.product = t3.product
order by
    t1.product

Related Questions

Sponsored Content

35 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

41 Answered Questions

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

32 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2850225 View
  • 1949 Score
  • 32 Answer
  • Tags:   sql duplicates

11 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

47 Answered Questions

30 Answered Questions

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

12 Answered Questions

[SOLVED] LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

Sponsored Content