Issues with sql Inner join

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

Answer 1

User77042963 posted

Post your query here.

Use INNER JOIN on the linked ids should work.

Answer 2

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

Answer 3

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

Answer 4

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  

Answer 5

User136609187 posted

Thanks a lot..worked like a charm