Left Join with Multiple Conditions and MAX Value

I'm trying to execute a left join where multiple conditions must be met with the inclusion of pulling in the MAX sequence number that meets those conditions.

The left join is on the unique identifier in both tables. Table acaps_history has several rows for each app_id. I need to pull in only one row with the highest seq_number and activity_code of 'XU'. If the code 'XU' doesn't exist for the given app_id, then the case statement above should return 'N' for that row. The code I have currently just isn't working - returning the error "a column may not be outer-joined to a subquery":

create table orig_play3 as   
   (select 
    x.*, 
    case when xa.activity_code in 'XU' then 'Y' else 'N' end as cpo_flag

    from 
    dfs_tab_orig_play_x x

    left join cf.acaps_history xa on 
    x.APP_ID = xa.FOC_APPL_ID
    and xa.activity_code in 'XU'
    and xa.seq_number = (select max(seq_number) from cf.acaps_history where FOC_APPL_ID=x.app_id)
    )

Given your error, it seems that the issue is the last part of your query:

and xa.seq_number = (select max(seq_number) from cf.acaps_history where FOC_APPL_ID=x.app_id)

This is still operating in the context of the ON clause, so the sub-query to find the max sequence number is the issue.

You should be able to avoid this by moving that sub-query out of the ON clause:

LEFT JOIN (
          SELECT FOC_APPL_ID, activity_code, seq_number
          FROM cf.acaps_history
          WHERE activity_code in 'XU'
) xa
ON x.APP_ID = xa.FOC_APPL_ID
WHERE xa.seq_number = (select max(ah.seq_number) from cf.acaps_history ah where ah.FOC_APPL_ID=x.app_id and ah.activity_code in 'XU')

LEFT JOIN on Max Value, It complains about s.studentid being an unknown field in the where clause of the sub query. Please suggest how I can achieve what I'm trying to  LEFT OUTER JOIN StudentGrades sd ON s.id=sd.student_id Sixteen student grade records will be returned by using only a LEFT OUTER JOIN in the query. Altering the query to include a subquery with MAX on record id, results with student latest GPA data.

This may be the most inefficient way to execute this query, but it worked... It took like 3 minutes to run (table size is over 600K rows), but again, it returned the results I needed:

create table test as (
  select x.*,
  case when xb.activity_code in 'XU' then 'Y' else 'N' end as cpo_flag

  from dfs_tab_orig_play_x x

   left join
       (select 
       xa.FOC_APPL_ID, xa.activity_code, xa.seq_number
       from dfs_tab_orig_play_x x, cf.acaps_history xa
       where x.app_id = xa.FOC_APPL_ID (+)
       and xa.seq_number = (select max(seq_number) from cf.acaps_history where 
       x.app_id=FOC_APPL_ID(+) and activity_code in 'XU')) xb
   on x.app_id = xb.FOC_APPL_ID (+)
 )

Examples with Multiple table left join using group by , max ,avg,order , SQL left join query examples using multiple tables. FROM `student6` a LEFT JOIN (SELECT student_id, MAX(mark) as mark FROM student_mark We will display list in the order of average value from maximum to minimum and then use two date ranges Some of the records by LIMIT query SQL SUM of data in columns. In the previous tutorial, you learned about the inner join that returns rows if there is, at least, one row in both tables that matches the join condition. The inner join clause eliminates the rows that do not match with a row of the other table. The left join, however, returns all rows from the left table whether or not there is a matching row in the right table. Suppose we have two tables A and B. The table A has four rows 1, 2, 3 and 4. The table B also has four rows 3, 4, 5, 6.

If you are on 12c, I like OUTER APPLY for this sort of thing, because it lets you sort the rows for each app_id descending by seq_number and then just pick the highest one.

SELECT
    x.*,
    CASE
            WHEN xa.activity_code IN 'XU' THEN 'Y'
            ELSE 'N'
        END
    AS cpo_flag
FROM
    dfs_tab_orig_play_x x
    OUTER APPLY ( SELECT *
                  FROM   cf.acaps_history xa
                  WHERE  xa.foc_appl_id = x.app_id
                  AND    xa.activity_code = 'XU'
                  ORDER BY xa.seq_number DESC
                  FETCH FIRST 1 ROW ONLY ) xa

Note: this logic is a little different from what you posted. In this version, it will join to the acaps_history row having the highest seq_number from among 'XU' records for the given app_id. Your version was joining to the row having the highest seq_number for the given app_id, whether that row was an 'XU' row or not. I am assuming (with little reason) that that was a bug on your part. But, if it wasn't, my version won't work as given.

Using JOIN with MAX to write efficient queries, How to join two SQL tables and display the latest results. The answer is to use JOIN and MAX statements in a query. Sixteen student grade records will be returned by using only a LEFT OUTER JOIN in the query. Altering  Oracle LEFT JOIN with USING clause. The USING clause specifies which column to test for equality when you join tables. The following shows the syntax of the LEFT JOIN with the USING clause: SELECT column_list FROM T1 LEFT JOIN T2 USING (c1,c2,c3, ); In this statement, the columns listed in the USING clause must be presented in both T1 and T2 tables.

Query to select max value on join, Your existing query is close to something that you could use but you can get the result easily by making a few changes. By altering your query to use the APPLY  Left Join with Multiple Conditions and MAX Value. I'm trying to execute a left join where multiple conditions must be met with the inclusion of pulling in the MAX sequence number that meets those conditions. The left join is on the unique identifier in both tables. Table acaps_history has several rows for each app_id.

MySQL 8.0 Reference Manual :: 13.2.10.2 JOIN Clause, The maximum number of tables that can be referenced in a single join is 61. The NATURAL [LEFT] JOIN of two tables is defined to be semantically The USING join selects the coalesced value of corresponding columns, whereas the ON  Multiple joins can be described as follows; multiple join is a query that contains the same or different join types, which are used more than once. Thus, we gain the ability to combine multiple tables of data in order to overcome relational database issues.

SQLite Self-join - Joining a Table to Itself, Note that you should be familiar with INNER JOIN and LEFT JOIN clauses before going forward with The self-join compares values of the same or different columns in the same table. The employees table has two roles: employees and managers. SQLite AVG · SQLite COUNT · SQLite MAX · SQLite MIN · SQLite SUM  Re: left outer join using dax, Multiple to Multiple. Subscribe to RSS Feed. Email to a Friend. Report Inappropriate Content. ‎07-25-2017 05:24 PM. Just playing around with CROSSJOIN and UNION as well and maybe a GENERATE using the nonexisting and ROW to provide space for the BLANK value 🙂 but now I have to take some sleep. Hamburg - Germany.

Comments
  • Do you only need to check whether xa contains a record with a specific activity code for each x.APP_ID (or check whether the last record has this activity code)? If so, I'd suggest using a subquery instead of a left join.
  • I need to check if xa contains the code 'XU' for each x.app_id. If it does, pull in only the record with the max sequence number. The max sequence number is relevant when an app_id in xa has the code 'XU' listed more than once.
  • try smth like select x.*, (select decode(count(*), 0, 'N', 'Y') from cf.acaps_history xa where x.app_id = xa.foc_appl_id and xa.activity_code = 'XU') from dfs_tab_orig_play_x x. I can't see where you use some other data from xa, so noneed to outer-join it...
  • Running that code I get the error: "X"."APP_ID": invalid identifier. Do I need to redefine the driver table (dfs_tab_orig_play_x x) within the sub-query?
  • @A.Oli Ah, that makes sense. I believe you should be able to do that same check in the outer query's where clause though, since the join will have already been executed in that context. I've updated my answer, try that and let me know if you have better luck.
  • @A.Oli Ah, I think it's that the sequence number returned may have been for a record that didn't have activity_code set to 'XU'. I've added that in and tried to better qualify some of the columns in case a conflict of some sort was happening. If you're still having trouble with those changes though I'd recommend peeling off the WHERE clause or other parts of the query to see what data is getting returned and whether there's something that's accidentally getting filtered out incorrectly. Now that you're not getting an error it should be a little easier to debug it.