Nested Query

Question

I have a table that shows the net cost and selling cost depending on the weight.
The Slab of the rates are from Min, -45, +45, 100, 300, 500, 1000, Pivot

I created 8 queries to filter a different Slab of the table for example the query of Min is as below

SELECT RateAirlineSlabTable.RateAirlineid, RateAirlineSlabTable.RateSlab, RateAirlineSlabTable.NetRate, RateAirlineSlabTable.SellingRate
FROM RateAirlineSlabTable
WHERE (((RateAirlineSlabTable.RateSlab)="Min"));

Then to be able to show the slab in different columns I created a query that depends on this other 8 queries as below

SELECT RateAirlineTable.RateAirlineID, RateMinQuery.NetRate AS MinNR, RateMinQuery.SellingRate AS MinSR, [Rate-45Query].NetRate AS [-45NR], [Rate-45Query].SellingRate AS [-45SR], [Rate+45Query].NetRate AS [+45NR], [Rate+45Query].SellingRate AS [+45SR], [Rate+100Query].NetRate AS [+100NR], [Rate+100Query].SellingRate AS [+100SR], [Rate+300Query].NetRate AS [+300], [Rate+300Query].SellingRate AS [+300SR], [Rate+500Query].NetRate AS [+500NR], [Rate+500Query].SellingRate AS [+500SR], [Rate+1000Query].NetRate AS [+1000NR], [Rate+1000Query].SellingRate AS [+1000SR], RatePivotQuery.RateSlab AS [Pivot], RatePivotQuery.NetRate AS PivotNR, RatePivotQuery.SellingRate AS PivotSR
FROM RatePivotQuery RIGHT JOIN ([Rate+1000Query] RIGHT JOIN ([Rate+500Query] RIGHT JOIN ([Rate+300Query] RIGHT JOIN ([Rate+100Query] RIGHT JOIN ([Rate+45Query] RIGHT JOIN ([Rate-45Query] RIGHT JOIN (RateMinQuery RIGHT JOIN RateAirlineTable ON RateMinQuery.RateAirlineid = RateAirlineTable.RateAirlineID) ON [Rate-45Query].RateAirlineid = RateAirlineTable.RateAirlineID) ON [Rate+45Query].RateAirlineid = RateAirlineTable.RateAirlineID) ON [Rate+100Query].RateAirlineid = RateAirlineTable.RateAirlineID) ON [Rate+300Query].RateAirlineid = RateAirlineTable.RateAirlineID) ON [Rate+500Query].RateAirlineid = RateAirlineTable.RateAirlineID) ON [Rate+1000Query].RateAirlineid = RateAirlineTable.RateAirlineID) ON RatePivotQuery.RateAirlineid = RateAirlineTable.RateAirlineID;

I have being trying to replace the 8 queries in this Main Query, but I cannot make it happen.

Can someone advise me how to do it.

Answer 1

Hi @Jose Ramirez ,

Welcome to Microsoft Q&A!

Please refer below and check whether it is working.

select a.RateAirlineID,  
max(case when b.RateSlab = 'Min' then b.NetRate end) as [MinNR],  
max(case when b.RateSlab = 'Min' then b.SellingRate end) as [MinSR],  
max(case when b.RateSlab = '-45' then b.NetRate end) as [-45NR],  
max(case when b.RateSlab = '-45' then b.SellingRate end) as [-45SR],  
max(case when b.RateSlab = '+45' then b.NetRate end) as [+45NR],  
max(case when b.RateSlab = '+45' then b.SellingRate end) as [+45SR],  
max(case when b.RateSlab = '+100' then b.NetRate end) as [+100NR],  
max(case when b.RateSlab = '+100' then b.SellingRate end) as [+100SR],  
max(case when b.RateSlab = '+300' then b.NetRate end) as [+300NR],  
max(case when b.RateSlab = '+300' then b.SellingRate end) as [+300SR],  
max(case when b.RateSlab = '+500' then b.NetRate end) as [+500NR],  
max(case when b.RateSlab = '+500' then b.SellingRate end) as [+500SR],  
max(case when b.RateSlab = '+1000' then b.NetRate end) as [+1000NR],  
max(case when b.RateSlab = '+1000' then b.SellingRate end) as [+1000SR],  
max(case when b.RateSlab not in ('Min','-45','+45','+100','+300','+500','+1000') then b.RateSlab end) as [Pivot],  
max(case when b.RateSlab not in ('Min','-45','+45','+100','+300','+500','+1000') then b.NetRate end) as [PivotNR],  
max(case when b.RateSlab not in ('Min','-45','+45','+100','+300','+500','+1000') then b.SellingRate end) as [PivotSR]  
from RateAirlineTable a  
left join  RateAirlineslabTable b  
on a.RateAirlineID=b.RateAirlineid  
group by a.RateAirlineID  

Output:

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

@MelissaMa-MSFT

I copied in Access, but I understood the coding because I tried it before.

As I said my intention was to show in one row all the possible breakdowns.

Don't worry anymore so far the way I fixed is to keep the same queries to create a new table and then delete them once I have what I need.

Answer 3

@MelissaMa-MSFT and @DBG

In below link you can download the sample of I'm intending to achieve
https://1drv.ms/u/s!Au4uCHYbhednjNJjoMEX_eG965gL9w?e=FKZg76

Answer 4

Hi @MelissaMa-MSFT yes, you are totally right

Thanks a lot for your help

Answer 5

@MelissaMa-MSFT thanks a lot for your support.

I think before didn't appeared because my answer was selected, but now appeared the options that you mentioned.

I hope I did it correctly this time.

Regards,
JC Ramirez