Welcome Guest ( Log In | Register )

Outline · [ Standard ] · Linear+

SQL how is the sequence of writing Nested Select?, in SQL

views
     
TSk town shit
post May 21 2019, 05:52 PM, updated 5y ago

Enthusiast
*****
Junior Member
757 posts

Joined: Jan 2009
hi Sifu, I'm learning SQL, to be more specific, T-SQL.
Nested Select statement is what I'm trying now, at first I thought we should write Nested SELECT right from bottom to up, but I found the sample script below actually no wrote from bottom to top, as I can see the aliases a b c d e, they are jumping everywhere, e.g. a is at the bottom
so I need advise from Sifus, practically how should I start in the Nested SELECT statement?

CODE
SELECT c.DocNo AS InvoiceNo, c.DocDate, c.CustomerName,
RTrim(e.FirstName) + ' ' + RTrim(e.LastName) AS SalesPerson
FROM dbo.Invoice AS c INNER JOIN dbo.Employees AS e
ON c.SlpCode = e.EmpNo
WHERE c.DocNo =
(SELECT MAX(b.DocNo)
 FROM dbo.Invoice AS b
 WHERE b.SlpCode = c.SlpCode AND b.DocDate =
  (SELECT MAX(a.DocDate) FROM dbo.Invoice AS a
   WHERE a.SlpCode = c.SlpCode))

kevinlim001
post May 22 2019, 07:55 AM

Ethical Hacker
*******
Senior Member
6,118 posts

Joined: May 2006
From: Planet called "EARTH"



I dont see a prob with the query. The way we structure nested select is from inside to outside.
SELECT * FROM C WHERE C.FIELDA = (SELECT C.FIELDA FROM B WHERE B.FIELDB = (SELECT A.FIELDA FROM A WHERE C.FIELDC))

Alternatively, on the query i think this could be something that can be done also.
CODE
SELECT
TOP 1
c.DocNo AS InvoiceNo,
c.DocDate,
c.CustomerName,
RTrim(e.FirstName) + ' ' + RTrim(e.LastName) AS SalesPerson
FROM
dbo.Invoice AS c
INNER JOIN dbo.Employees AS e ON c.SlpCode = e.EmpNo
ORDER BY
c.DocDate DESC, c.DocNo DESC



also, a b c are just table alias. You can put other alias like TABINV, TABEMP something like but being able to see the arrangement of the order of ABC you can tell how it is structured. Good for your understanding and learning. For advance programer to debug they will always start with the inner most select and start debugging layer by layer.

This post has been edited by kevinlim001: May 22 2019, 08:03 AM
bumpo
post May 22 2019, 10:28 AM

On my way
****
Junior Member
632 posts

Joined: Mar 2013


alias are just an alternate representation. usually used to reduce the query length. there is no sequence structure to it, as such you dont need to follow a, b, c. it can even be more than one char like tbl1

on nested select, it is just a query that returns result that another query uses. you can have multiple nested and also more than one nested per query as well.
you should take note on the number of rows it returns. in your case, it MUST return a single value (one row, one column). If anything more, the DB will scold you laugh.gif

SELECT c.DocNo AS InvoiceNo, c.DocDate, c.CustomerName, RTrim(e.FirstName) + ' ' + RTrim(e.LastName) AS SalesPerson FROM dbo.Invoice AS c INNER JOIN dbo.Employees AS e ON c.SlpCode = e.EmpNo WHERE c.DocNo = (SELECT MAX(b.DocNo) FROM dbo.Invoice AS b WHERE b.SlpCode = c.SlpCode AND b.DocDate = (SELECT MAX(a.DocDate) FROM dbo.Invoice AS a WHERE a.SlpCode = c.SlpCode))
TSk town shit
post May 23 2019, 07:25 PM

Enthusiast
*****
Junior Member
757 posts

Joined: Jan 2009
QUOTE(kevinlim001 @ May 22 2019, 06:55 AM)
I dont see a prob with the query. The way we structure nested select is from inside to outside.
SELECT * FROM C WHERE C.FIELDA = (SELECT C.FIELDA FROM B WHERE B.FIELDB = (SELECT A.FIELDA FROM A WHERE C.FIELDC))

Alternatively, on the query i think this could be something that can be done also.
CODE
SELECT
TOP 1
c.DocNo AS InvoiceNo,
c.DocDate,
c.CustomerName,
RTrim(e.FirstName) + ' ' + RTrim(e.LastName) AS SalesPerson
FROM
dbo.Invoice AS c
INNER JOIN dbo.Employees AS e ON c.SlpCode = e.EmpNo
ORDER BY
c.DocDate DESC, c.DocNo DESC

also, a b c are just table alias. You can put  other alias like TABINV, TABEMP something like but being able to see the arrangement of the order of ABC you can tell how it is structured. Good for your understanding and learning. For advance programer to debug they will always start with the inner most select and start debugging layer by layer.
*
you said from inside to outside, so my guess from bottom to up is correct, am I right?
and I just wondered why the person who wrote that query, he never have the lowest Alias becomes a
kevinlim001
post May 24 2019, 08:43 AM

Ethical Hacker
*******
Senior Member
6,118 posts

Joined: May 2006
From: Planet called "EARTH"



QUOTE(k town shit @ May 23 2019, 07:25 PM)
you said from inside to outside, so my guess from bottom to up is correct, am I right?
and I just wondered why the person who wrote that query, he never have the lowest AliasĀ  becomes a
*
its not bottom up. you always debug or start writing from the inner most query (deepest nest). I dont understand what do you mean by lowest alias. The way he build it he do it correctly for normal logic. (c(b(a))). You shd not be bothered too much by how he sequence his table alias. He is a give u a perfect example but in most of the case you need to understand how the statement is structured as in real world ppl might not be using A B C or they sequence is not as good as the example you provided.

The reason i said is not bottom up because you could have query like this


CODE

SELECT * FROM TableB b WHERE ValueBA = (SELECT a.ValueBA FROM TableA a WHERE a.ValueAB >60 AND a.ValueAB < 80)
UNION
SELECT * FROM TableB b WHERE ValueBA = (SELECT a.ValueBA FROM TableA a WHERE a.ValueAB >10 AND a.ValueAB < 30)


This post has been edited by kevinlim001: May 24 2019, 08:44 AM

 

Change to:
| Lo-Fi Version
0.0121sec    0.28    5 queries    GZIP Disabled
Time is now: 29th March 2024 - 01:02 AM