Table Alias in SQL Advanced Query

Table Alias in SQL Advanced Query

  

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


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.

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.