Skip to Content (Press Enter)
UserImage.jpg
JaySmooth
379Views
2Comments
Table Alias in SQL Advanced Query
Question
SQL

Hi,


So I'am using the advanced query to get the top 1 record of the many relationship table of my record. Below is my query:

SELECT {primaryTable}.[id]
            ,{primaryTable}.[name]
            ,{primaryTable}.[description]
    FROM {primaryTable} WITH(NOLOCK)
    INNER JOIN 
(
select MAX({manyTable}.[Id]) AS myMaxId
FROM {manyTable}
WHERE {manyTable}.[type] = @paramHere
) AS errorhere
ON errorhere.myMaxId = {primaryTable}.[id]


My problem is I'am having error in assigning a table alias to join to my primary table.


Any help would be helpful.


Regards,

Ismael


0
0
15 Sep 2018
Copy Link
2019-09-24 18-41-25
Jorge Martins
 
MVP

Hi Ismael,

I seem to remember the AS is not supposed to be there on JOINS? So your query would look like this:

SELECT {primaryTable}.[id]
            ,{primaryTable}.[name]
            ,{primaryTable}.[description]
    FROM {primaryTable} WITH(NOLOCK)
    INNER JOIN (
        SELECT MAX({manyTable}.[Id]) AS myMaxId
        FROM {manyTable}
        WHERE {manyTable}.[type] = @paramHere
    ) errorhere ON errorhere.myMaxId = {primaryTable}.[id]

But... what are you trying to achieve?? It seems this could be done with a WHERE clause and no need to JOIN. The following SQL returns the {primaryTable} entity's record that corresponds to the highest [Id] in the {manyTable} entity (I'm assuming that {primaryTable}.[id] and {manyTable}.[Id] are of the same exact data type):

SELECT {primaryTable}.[id],
       {primaryTable}.[name],
       {primaryTable}.[description]
FROM {primaryTable}
WHERE {primaryTable}.[id] =
    (SELECT MAX({manyTable}.[Id])
    FROM {manyTable}
    WHERE {manyTable}.[type] = @paramHere)

By the way, you might be interested in reading about transaction isolation levels in OutSystems.

0
0
15 Sep 2018
Copy Link
2020-07-21 19-28-50
Rajat Agrawal
Champion

Jorge Martins wrote:

Hi Ismael,

I seem to remember the AS is not supposed to be there on JOINS? So your query would look like this:

SELECT {primaryTable}.[id]
            ,{primaryTable}.[name]
            ,{primaryTable}.[description]
    FROM {primaryTable} WITH(NOLOCK)
    INNER JOIN (
        SELECT MAX({manyTable}.[Id]) AS myMaxId
        FROM {manyTable}
        WHERE {manyTable}.[type] = @paramHere
    ) errorhere ON errorhere.myMaxId = {primaryTable}.[id]

But... what are you trying to achieve?? It seems this could be done with a WHERE clause and no need to JOIN. The following SQL returns the {primaryTable} entity's record that corresponds to the highest [Id] in the {manyTable} entity (I'm assuming that {primaryTable}.[id] and {manyTable}.[Id] are of the same exact data type):

SELECT {primaryTable}.[id],
       {primaryTable}.[name],
       {primaryTable}.[description]
FROM {primaryTable}
WHERE {primaryTable}.[id] =
    (SELECT MAX({manyTable}.[Id])
    FROM {manyTable}
    WHERE {manyTable}.[type] = @paramHere)

By the way, you might be interested in reading about transaction isolation levels in OutSystems.

I agreed with Jorge.


1
0
15 Sep 2018
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.