locked
Issues with sql Inner join RRS feed

  • Question

  • User136609187 posted

    Iam having the below sample tables.Just trying to join all these table to get a final report.Please find the details below

    ProductMaster

    ProductId       ProductName       Brand 
    221                Router                    Dlink 
    224                UPS                        APC 

    ProductStateDetais
    Id       ProductId    StateId
    11         221             11
    12         224             17

    StateMaster
    StateId          State
    11              TamilNadu
    15              Maharashtra
    17              Karnataka

    AreaMaster

    AreaId            Area 
    122                Chennai 
    115                Salem 
    134                Thiruppur 
    119                Madurai 
    177                Mysore 
    178               Bangalore 

    LocationMaster

    LocationId       Location 
    55                    Tambaram 
    65                    Attur 
    77                    Ashokpuram 
    78                    Koramangala 
    88                    Melur 
    86                    Dharapuram 
    87                    Egmore 

    ProductRequestMaster
    RequestId        ProductId      AreaId        LocationId
    MR012B51       221                122                55
    MR012B77       221                115                65
    MR012B87       221                119                88
    MR012B11       224                177                77
    MR012B21       224                178                78

    Finally i need to generate a report like the one below which is basically all the requests from the ProductRequestMaster table by joining all the related tables.

    I tried doing this using inner and outer joins but getting lot of duplicate records same request coming up in multiple rows. Dont have much knowledge on sql to try further.

    Please can anyone help me out here with a sample query . I need to generate a report like the below .

    RequestId          ProductName      Brand            State               Area            Location                    
    MR012B51          Router                    Dlink           Tamilnadu        Chennai        Tambaram                
    MR012B77          Router                    Dlink           Tamilnadu         Salem           Attur 
    MR012B87          Router                    Dlink           Tamilnadu        Madurai        Melur 
    MR012B11          UPS                        APC             Karnataka        Mysore          Ashokpuram 
    MR012B21          UPS                        APC             Karnataka        Bangalore      Koramangala 

    Many Thanks

    Wednesday, March 16, 2016 5:37 PM

Answers

  • User-219423983 posted

    Hi chithra_iyer,

    You could have a look at below complete code and it works on my client. In the code, I have modified your code and provide a new method that’s easy to know the query.

    declare @ProductMaster table (ProductId int,ProductName  varchar(20),Brand varchar(20))
    insert into @ProductMaster values (221,'Router','Dlink'),(224,'UPS','APC')
    
    declare @ProductStateDetails table (Id int,ProductId int,StateId int)
    insert into @ProductStateDetails values (11,221,11),(12,224,17)
    
    declare @StateMaster table(StateId int, [State] varchar(20))
    insert into @StateMaster values(11,'TamilNadu'),(15,'Maharashtra'),(17,'Karnataka')
    
    declare @AreaMaster table(AreaId int, Area varchar(20))
    insert into @AreaMaster values (122,'Chennai')
    insert into @AreaMaster values (115,'Salem')
    insert into @AreaMaster values ( 134,'Thiruppur') 
    insert into @AreaMaster values ( 119,'Madurai')
    insert into @AreaMaster values ( 177,'Mysore')
    insert into @AreaMaster values ( 178,'Bangalore') 
    
    declare @LocationMaster table(LocationId int, Location varchar(20))
    insert into @LocationMaster values (55,'Tambaram')
    insert into @LocationMaster values (65,'Attur') 
    insert into @LocationMaster values ( 77,'Ashokpuram') 
    insert into @LocationMaster values ( 78,'Koramangala') 
    insert into @LocationMaster values ( 88,'Melur') 
    insert into @LocationMaster values ( 86,'Dharapuram') 
    insert into @LocationMaster values ( 87,'Egmore')
    
    declare @ProductRequestMaster table (RequestId varchar(20),ProductId int,AreaId int,LocationId int)
    insert into @ProductRequestMaster values ('MR012B51',221,122,55)
    insert into @ProductRequestMaster values ('MR012B77',221,115,65)
    insert into @ProductRequestMaster values ('MR012B87',221,119,88)
    insert into @ProductRequestMaster values ('MR012B11',224,177,77)
    insert into @ProductRequestMaster values ('MR012B21',224,178,78)
    
    
    ------------------Your modified code----------------
    SELECT f.RequestId, d.ProductName, d.Brand, b.State, a.Area, e.Location
     FROM 
     @AreaMaster a INNER JOIN
     @StateMaster b INNER JOIN
     @ProductStateDetails c INNER JOIN
     @ProductMaster d ON c.ProductId = d.ProductId ON b.StateId = c.StateId inner JOIN
     @LocationMaster e INNER JOIN
     @ProductRequestMaster f ON e.LocationId = f.LocationId ON d.ProductId = f.ProductId ON 
     a.AreaId = f.AreaId
    
    
    ---------------Abother code----------------
    SELECT TMP1.RequestId, TMP2.ProductName, TMP2.Brand, TMP2.[State],TMP1.Area, TMP1.Location
    FROM 
    (
    	select PRM.RequestId, PRM.ProductId,AM.Area, LM.Location
    	from 
    	  @ProductRequestMaster PRM,
    	  @AreaMaster AM, @LocationMaster LM
    	where PRM.AreaId = AM.AreaId AND PRM.LocationId = LM.LocationId
    ) AS TMP1
    Left join
    (
    	select PSD.ProductId, PM.ProductName, PM.Brand, SM.[State]
    	from
    	  @ProductStateDetails PSD,
    	  @ProductMaster PM,@StateMaster SM
    	where PSD.ProductId = PM.ProductId and PSD.StateId = sm.StateId
    ) AS TMP2
    On TMP1.ProductId = TMP2.ProductId
    

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 17, 2016 6:34 AM

All replies

  • User77042963 posted

    Post your query here.

    Use INNER JOIN on the linked ids should work.

    Wednesday, March 16, 2016 6:09 PM
  • User136609187 posted

    Here is the query i tried with both inner and outer join

    SELECT f.RequestId, d.ProductName, d.Brand, b.State, a.Area, e.Location,
    c.StateId
    FROM dbo.AreaMaster a FULL OUTER JOIN
    dbo.StateMaster b INNER JOIN
    dbo.ProductStateDetails c INNER JOIN
    dbo.ProductMaster d ON c.ProductId = d.ProductId ON b.StateId = c.StateId RIGHT OUTER JOIN
    dbo.LocationMaster e INNER JOIN
    dbo.ProductRequestMaster f ON e.LocationId = f.LocationId ON d.ProductId = f.ProductId ON
    a.AreaId = f.AreaId

    Thursday, March 17, 2016 2:49 AM
  • User-219423983 posted

    Hi chithra_iyer,

    You could have a look at below complete code and it works on my client. In the code, I have modified your code and provide a new method that’s easy to know the query.

    declare @ProductMaster table (ProductId int,ProductName  varchar(20),Brand varchar(20))
    insert into @ProductMaster values (221,'Router','Dlink'),(224,'UPS','APC')
    
    declare @ProductStateDetails table (Id int,ProductId int,StateId int)
    insert into @ProductStateDetails values (11,221,11),(12,224,17)
    
    declare @StateMaster table(StateId int, [State] varchar(20))
    insert into @StateMaster values(11,'TamilNadu'),(15,'Maharashtra'),(17,'Karnataka')
    
    declare @AreaMaster table(AreaId int, Area varchar(20))
    insert into @AreaMaster values (122,'Chennai')
    insert into @AreaMaster values (115,'Salem')
    insert into @AreaMaster values ( 134,'Thiruppur') 
    insert into @AreaMaster values ( 119,'Madurai')
    insert into @AreaMaster values ( 177,'Mysore')
    insert into @AreaMaster values ( 178,'Bangalore') 
    
    declare @LocationMaster table(LocationId int, Location varchar(20))
    insert into @LocationMaster values (55,'Tambaram')
    insert into @LocationMaster values (65,'Attur') 
    insert into @LocationMaster values ( 77,'Ashokpuram') 
    insert into @LocationMaster values ( 78,'Koramangala') 
    insert into @LocationMaster values ( 88,'Melur') 
    insert into @LocationMaster values ( 86,'Dharapuram') 
    insert into @LocationMaster values ( 87,'Egmore')
    
    declare @ProductRequestMaster table (RequestId varchar(20),ProductId int,AreaId int,LocationId int)
    insert into @ProductRequestMaster values ('MR012B51',221,122,55)
    insert into @ProductRequestMaster values ('MR012B77',221,115,65)
    insert into @ProductRequestMaster values ('MR012B87',221,119,88)
    insert into @ProductRequestMaster values ('MR012B11',224,177,77)
    insert into @ProductRequestMaster values ('MR012B21',224,178,78)
    
    
    ------------------Your modified code----------------
    SELECT f.RequestId, d.ProductName, d.Brand, b.State, a.Area, e.Location
     FROM 
     @AreaMaster a INNER JOIN
     @StateMaster b INNER JOIN
     @ProductStateDetails c INNER JOIN
     @ProductMaster d ON c.ProductId = d.ProductId ON b.StateId = c.StateId inner JOIN
     @LocationMaster e INNER JOIN
     @ProductRequestMaster f ON e.LocationId = f.LocationId ON d.ProductId = f.ProductId ON 
     a.AreaId = f.AreaId
    
    
    ---------------Abother code----------------
    SELECT TMP1.RequestId, TMP2.ProductName, TMP2.Brand, TMP2.[State],TMP1.Area, TMP1.Location
    FROM 
    (
    	select PRM.RequestId, PRM.ProductId,AM.Area, LM.Location
    	from 
    	  @ProductRequestMaster PRM,
    	  @AreaMaster AM, @LocationMaster LM
    	where PRM.AreaId = AM.AreaId AND PRM.LocationId = LM.LocationId
    ) AS TMP1
    Left join
    (
    	select PSD.ProductId, PM.ProductName, PM.Brand, SM.[State]
    	from
    	  @ProductStateDetails PSD,
    	  @ProductMaster PM,@StateMaster SM
    	where PSD.ProductId = PM.ProductId and PSD.StateId = sm.StateId
    ) AS TMP2
    On TMP1.ProductId = TMP2.ProductId
    

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 17, 2016 6:34 AM
  • User77042963 posted

    Here is the query i tried with both inner and outer join

    SELECT f.RequestId, d.ProductName, d.Brand, b.State, a.Area, e.Location,
    c.StateId
    FROM dbo.AreaMaster a FULL OUTER JOIN
    dbo.StateMaster b INNER JOIN
    dbo.ProductStateDetails c INNER JOIN
    dbo.ProductMaster d ON c.ProductId = d.ProductId ON b.StateId = c.StateId RIGHT OUTER JOIN
    dbo.LocationMaster e INNER JOIN
    dbo.ProductRequestMaster f ON e.LocationId = f.LocationId ON d.ProductId = f.ProductId ON
    a.AreaId = f.AreaId

    Here is the right JOINs:

    SELECT f.RequestId, d.ProductName, d.Brand, b.State, a.Area, e.Location --,c.StateId
    FROM ProductRequestMaster f 
    JOIN ProductMaster d ON f.ProductId = d.ProductId 
    JOIN ProductStateDetais c ON  c.ProductId = d.ProductId 
    JOIN StateMaster b on  b.StateId = c.StateId
    JOIN AreaMaster a  ON a.AreaId = f.AreaId
    JOIN LocationMaster e ON e.LocationId = f.LocationId  

    Thursday, March 17, 2016 2:14 PM
  • User136609187 posted

    Thanks a lot..worked like a charm

    Friday, March 18, 2016 3:29 AM