Nested Query

Jose Ramirez 21 Reputation points
2021-08-23T10:16:01.89+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,710 questions
Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
821 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-08-24T02:29:56.7+00:00

    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:
    125798-output.png

    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.

    0 comments

8 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. DBG 2,301 Reputation points
    2021-08-23T16:29:27.593+00:00

    Hi. I don't know about the others, but I am having a hard time picturing what you're trying to do. Can you maybe post some screenshots?

    0 comments
  2. Viorel 112.1K Reputation points
    2021-08-23T16:36:44.073+00:00

    Try something like this: 

    select s.RateAirlineID,
    max(case when s.RateSlab = 'Min' then s.NetRate end) as [MinNR],
    max(case when s.RateSlab = 'Min' then s.SellingRate end) as [MinRS],
    max(case when s.RateSlab = '-45' then s.NetRate end) as [-45NR],
    max(case when s.RateSlab = '-45' then s.SellingRate end) as [-45RS]
    -- etc. ---
from RateAirlineTable t
inner join RateAirlineSlabTable s on s.RateAirlineID = t.RateAirlineID
group by s.RateAirlineID

    If this query does not quite work, show some sample tables and data.

  3. Jose Ramirez 21 Reputation points
    2021-08-25T02:31:26.66+00:00

    @MelissaMa-MSFT

    Thanks for your proposal, but I copied and past your SQL and says there is syntax error. But what you are trying to achieve I already tried and the problem is that I will have two lines with the same RateAirlineID for showing different Slab.

    The idea is to show only one RateAirlineID row with all the breakdowns.

    Seems that the best approach is as I was doing it

    0 comments
  4. MelissaMa-MSFT 24,176 Reputation points
    2021-08-25T03:07:10.297+00:00

    Hi @Jose Ramirez ,

    Thanks for your update.

    Where did you copy and paste my sql statement? In the Access or SQL Server?

    If it is Access, its syntax is a little different from that of SQL Server.

    But what you are trying to achieve I already tried and the problem is that I will have two lines with the same RateAirlineID for showing different Slab.

    You could have a try to add one more column RateSlabID to list all the breakdowns like below: 

    select a.RateAirlineID,b.RateSlabID,  
 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,b.RateSlabID  
 order by a.RateAirlineID,b.RateSlabID

    Part of the output:

    126232-output.png

    If above is still not working, please provide the expected output and your syntax error snapshot.

    I am not familiar with Access, you could have a try to change all single quotes to double quotes and check whether it works.

    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.

    0 comments