Deciphering SQL (1 Viewer)

CedarTree

Registered User.
Local time
Yesterday, 22:18
Joined
Mar 2, 2018
Messages
404
Hi - working with data that came from an Oracle DB originally. Not sure if the SQL syntax is different, but there this coding that refers to "b". I suspect "b" is an alias (or maybe another query?) but here are the references to "b" I'm seeing. Any thoughts on what it means? Or is it a subset of Table1? Thanks.

Select max(b.date1) from TABLE1 b
where (a.field1 = b.field1 and
a.fieldx = (Select max(b.fieldx) from TABLE1 b
where (a.field1 = b.field1 and
a.field2= b.field2)
 

Isaac

Lifelong Learner
Local time
Yesterday, 19:18
Joined
Mar 14, 2017
Messages
8,738
Yes, I think that b is just an alias. Looks the same as T-SQL in regard to how to alias a table.
 

Micron

AWF VIP
Local time
Yesterday, 22:18
Joined
Oct 20, 2018
Messages
3,476
That's all of it? Looks like it might be a sub query referencing the same table twice but it seems you left off part of the query sql. Then again, I'm not familiar with sql outside of Access. Anyway, when you need to SELECT a value from the same domain (as is often done in an inner SELECT statement) you have to alias one of the table references. More on sub queries here if you're interested.
 

CedarTree

Registered User.
Local time
Yesterday, 22:18
Joined
Mar 2, 2018
Messages
404
That was an excerpt. Thanks for the help. But wouldn't an alias be written as "TABL1 AS ALIAS"?
 

Isaac

Lifelong Learner
Local time
Yesterday, 19:18
Joined
Mar 14, 2017
Messages
8,738
No, the as is superfluous and not required in PL- or T-SQL, although many people still use it.
 

Micron

AWF VIP
Local time
Yesterday, 22:18
Joined
Oct 20, 2018
Messages
3,476
Not sure, but I think it's not required in Access either but since I always use it, I don't know. I defer to the side of caution in most things like this because M$ has a habit of turning working but 'less than perfect' syntax into gibberish when they update things.
 

isladogs

MVP / VIP
Local time
Today, 02:18
Joined
Jan 14, 2017
Messages
18,186
Although I always use AS before an alias for clarity, it is definitely NOT required in Access.
The full query should of course have code to indicate what 'a' is an alias for.
 

kevlray

Registered User.
Local time
Yesterday, 19:18
Joined
Apr 5, 2010
Messages
1,046
Also re-using the same alias name in a sub-query can cause confusion.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 13:18
Joined
Jan 20, 2009
Messages
12,849
Also re-using the same alias name in a sub-query can cause confusion.

That is just confusion for the developer though.
If you want real confusion, try mixing main query aliases into an OUTER APPLY.:eek:

I don't use aliases much any more, except where essential. The databases I have to work with have too many extremely similar table names and the queries can involve many of them at once.
 

cheekybuddha

AWF VIP
Local time
Today, 02:18
Joined
Jul 21, 2014
Messages
2,237
I don't understand how the query you posted will parse properly.

Where is the table aliased as 'a'?

Also your parentheses in the WHERE clause don't match.

Reformatted for readability:
SQL:
Select 
  max(b.date1) 
from TABLE1 b
where (a.field1 = b.field1 
  and a.fieldx = (
    Select 
      max(b.fieldx) 
    from TABLE1 b
    where (a.field1 = b.field1 
      and a.field2= b.field2
  )

The open paren after the first WHERE is not necessary, but if it is included, then another closing paren is required at the end of the statement.

Sometimes it's easier just to post your actual query rather than use pseudo-sql into which you might introduce other errors!
 

Users who are viewing this thread

Top Bottom