• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Deciphering SQL (1 Viewer)

CedarTree

Registered User.
Local time
Today, 10:28
Joined
Mar 2, 2018
Messages
246
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
Today, 07:28
Joined
Mar 14, 2017
Messages
2,375
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
Today, 10:28
Joined
Oct 20, 2018
Messages
3,469
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
Today, 10:28
Joined
Mar 2, 2018
Messages
246
That was an excerpt. Thanks for the help. But wouldn't an alias be written as "TABL1 AS ALIAS"?
 

Micron

AWF VIP
Local time
Today, 10:28
Joined
Oct 20, 2018
Messages
3,469
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

CID VIP
Local time
Today, 15:28
Joined
Jan 14, 2017
Messages
13,933
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
Today, 07:28
Joined
Apr 5, 2010
Messages
854
Also re-using the same alias name in a sub-query can cause confusion.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:28
Joined
Jan 20, 2009
Messages
12,078
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, 15:28
Joined
Jul 21, 2014
Messages
557
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