How can I use SUBSELECT with JOIN?

left join subquery mysql
how to join two subqueries in sql
when to use subquery and when to use joins
when to use subquery in sql
inner join subquery
mysql subquery join
subquery vs join performance oracle
how to join a table with a subquery

I want to build an OpenSQL query to return only the max stat value for all objnr. This works:

    SELECT O~OBJNR
      FROM JCDS AS O
      WHERE O~OBJNR = 'Obj12345'
      AND STAT = ( SELECT MAX(STAT) 
                     FROM JCDS AS I 
                     WHERE I~OBJNR = O~OBJNR ).

However, if I try to use a join, and not provide objnr directly, I get an error message:

    SELECT O~OBJNR, O~STAT
      FROM JCDS AS O
      INNER JOIN AFVC 
          ON AFVB~OBJNR = O~OBJNR
      WHERE "AUFPL" = 'Aufpl12345' 
      AND O~STAT = ( SELECT MAX(STAT) 
                       FROM JCDS AS I 
                       WHERE I~OBJNR = O~OBJNR ).

The error message is meaningless; "SQL error"

you could simplify your query like this by grouping by the objectnumbers:

SELECT O.OBJNR, MAX(O.STAT)
FROM JCDS AS O
GROUP BY O.OBJNR

Depending on which columns you need to display from you joined tables you could either extend the group by:

SELECT O.OBJNR, A.SOMECOL, MAX(O.STAT)
FROM JCDS AS O
INNER JOIN AFVC AS A
ON AFVB.OBJNR = O.OBJNR
GROUP BY O.OBJNR, A.SOMECOL

or use a subquery:

SELECT MAXSTAT.OBJNR, MAXSTAT.STAT, A.SOMECOL
FROM (
SELECT O.OBJNR, MAX(O.STAT) STAT
FROM JCDS AS O
GROUP BY O.OBJNR ) MAXSTAT
INNER JOIN AFVC AS A
ON AFVB.OBJNR = MAXSTAT.OBJNR

How to use subquery in JOIN operation in MySQL, Note that the left and right table of the join keyword must both return a common key that can be used for the join. Also note that, using subquery in JOIN operation should generally be avoided if you can rewrite your query in a different way, the reason being that no indexes can be used on a temporary table in memory. select o.objnr, a.somecol, max(o.stat) from jcds as o inner join afvc as a on afvb.objnr = o.objnr group by o.objnr, a.somecol or use a subquery: select maxstat.objnr, maxstat.stat, a.somecol from ( select o.objnr, max(o.stat) stat from jcds as o group by o.objnr ) maxstat inner join afvc as a on afvb.objnr = maxstat.objnr

Are you looking for the record with the highest stat per objnr? You can use a window function for this:

select *
from
(
  select
    jcds.*,
    max(stat) over (partition by objnr) as max_stat
  from jcds
) data
where stat = max_stat;

What is the Difference between a Join and Subquery?, Read this article to understand when to use a join versus a subquery in your Joins and subqueries are both be used to query data from different tables and  The following examples illustrate the use of joins in a subselect query. Example 1: This example illustrates the results of the various joins using tables J1 and J2. These tables contain rows as shown.

The subselect works flawlessly with a JOIN, if I do not mistype the tablename:

    SELECT O~OBJNR, O~STAT
      FROM JCDS AS O
      INNER JOIN AFVC 
    --    ON afvB~OBJNR = O~OBJNR
    --          |
          ON afvC~OBJNR = O~OBJNR
      WHERE "AUFPL" = 'Aufpl12345' 
      AND O~STAT = ( SELECT MAX(STAT) 
                       FROM JCDS AS I 
                       WHERE I~OBJNR = O~OBJNR )

Sub Select & Inner Join - Databases, Guys I am struggling with a sub select in an inner join. and then you use IS NULL to return only those rows from the left table which had no match in the right​  Subqueries are advantageous over joins when you have to calculate an aggregate value on-the-fly and use it in the outer query for comparison. Example 1 shows this. EXAMPLE 1. Get the employee numbers and enter dates of all employees with enter dates equal to the earliest date:

The Power of Subqueries, You can use the virtual table columns in the outer query using the alias A. for example A.numActivities. Joining more than one virtual Table (SQL Server). The next  Using a Subquery in the FROM clause When subqueries are used in the FROM clause they act as a table that you can use to select columns and join to other tables. Because of this, some people argue they really aren’t subqueries, but derived tables.

Converting Subqueries to Joins, If you can avoid a subquery and replace it with a JOIN clause, you should do so without hesitation. But of course, in some cases, using a  A JOIN clause is used to combine rows from two or more tables, based on a related column between them. Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column. Then, we can create the following SQL statement (that

Using subqueries instead of joins, The following statement obtains the same results using a subquery instead of a join: SELECT SalesOrders.ID, SalesOrders.OrderDate, ( SELECT CompanyName  A Correlated subquery, or for that matter any subquery, can use a different table than the outer query. This can come in handy when you’re working with a “parent” table, such as SalesOrderHeader, and you want to include in result a summary of child rows, such as those from SalesOrderDetail.

Comments
  • could you please post the error message that thrown to you
  • I deleted my comment, hope it is expected as an answer :)
  • can you confirm your question was about Open SQL, because your syntax with . after table aliases is invalid for Open SQL, you should use ~ instead (also other syntax issues).