Joins - two tables

Question

I am new to Databases. I came across a peculiar problem with two tables. Please let me know the solution. Please fnd the scenario below

a ProductCentre table prdcntrId (primary key), prdcntrname

a ApplicationType table apptypeid (primary key) prdcntreid(foreign key to ProductCentre ) apptypname

ProductCentre table       ||            ApplicationType table
                          ||
 prdcntrId  prdcntrname   ||       apptypeid prdcntreid  apptypname
001          Delhi        ||          11          001           Busines
002          Mumbai       ||          12          003           Engg
003          Hyd          ||          13          001          Soft
                                      14          002         Science

The end result should be like this A productcentre can have any type of applications like Delhi can have many busines, soft applications same with mumbai, hyd

---------------------------------------------------------------------
prdcntrname                Busines     Engg   Soft       Science
---------------------------------------------------------------------
Delhi                      1             0       1          0
---------------------------------------------------------------------
Mumbai                     0             1       0          1
---------------------------------------------------------------------
Hyd                        0             1       0          0
---------------------------------------------------------------------

Is this solution possible from these two tables. Please help me in this scenario

Thanks, KK

Answer 1

You can try using a PIVOT

Something like (Sql Server)

DECLARE @ProductCentre table(
        prdcntrId INT,
        prdcntrname VARCHAR(50)
)

DECLARE @ApplicationType table(
         apptypeid INT,
         prdcntreid INT,
         apptypname VARCHAR(50)
)

INSERT INTO @ProductCentre SELECT 001,'Delhi'
INSERT INTO @ProductCentre SELECT 002,'Mumbai'
INSERT INTO @ProductCentre SELECT 003,'Hyd'

INSERT INTO @ApplicationType SELECT 11,001,'Busines'
INSERT INTO @ApplicationType SELECT 12,003,'Engg' 
INSERT INTO @ApplicationType SELECT 13,001,'Soft' 
INSERT INTO @ApplicationType SELECT 14,002,'Science' 

SELECT  p.*
FROM    @ProductCentre p INNER JOIN
        @ApplicationType a ON p.prdcntrId = a.prdcntreid
PIVOT
        (COUNT(apptypname) FOR apptypname IN ([Busines],
                                                  [Engg],
                                                  [Soft],
                                                  [Science])) p

Answer 2

If the `apptypname' types are fixed then this can work:

select
c.prdcntrname,
Busines = (select count(*) 
           from ApplicationType at 
           where at.prdcntreid = c.prdcntreid and apptypname = 'Business'),
Engg = (select count(*) 
           from ApplicationType at 
           where at.prdcntreid = c.prdcntreid and apptypname = 'Engg'),
Soft = (select count(*) 
           from ApplicationType at 
           where at.prdcntreid = c.prdcntreid and apptypname = 'Soft'),
Science = (select count(*) 
           from ApplicationType at 
           where at.prdcntreid = c.prdcntreid and apptypname = 'Science'),

from ProductCentre c
order by c.prdcntrname