Answered by:
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 APCProductStateDetais
Id ProductId StateId
11 221 11
12 224 17StateMaster
StateId State
11 TamilNadu
15 Maharashtra
17 KarnatakaAreaMaster
AreaId Area
122 Chennai
115 Salem
134 Thiruppur
119 Madurai
177 Mysore
178 BangaloreLocationMaster
LocationId Location
55 Tambaram
65 Attur
77 Ashokpuram
78 Koramangala
88 Melur
86 Dharapuram
87 EgmoreProductRequestMaster
RequestId ProductId AreaId LocationId
MR012B51 221 122 55
MR012B77 221 115 65
MR012B87 221 119 88
MR012B11 224 177 77
MR012B21 224 178 78Finally 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 KoramangalaMany 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.AreaIdThursday, 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.AreaIdHere 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