SQL query to select single record when record may exist in another colum-CodePudding

i just need help putting together a query. Below is an example of what I would like to achieve:

Table name: Current_Orders_tbl

Indented output query

We can get an order without a transaction_id, or an order that has an order_id that is the same as a transaction_id in the table.

When I query the table, I would like to only see:

The order_id that contains a transaction_id that matches an already used order_id.
The null transaction_id if that order_id hasn't been used before

Thanks in advance

CodePudding user response：

SELECT COALESCE(table2.order_id, table1.order_Id) AS order_id, 
    table1.order_name, 
    COALESCE(table2.transaction_id, table1.transaction_id) AS transaction_id
FROM current_orders_tbl AS table1 LEFT OUTER JOIN
    Current_orders_tbl AS table2 ON table1.order_id = table2.transaction_iD
WHERE 
    (table1.transaction_id IS NULL and table2.order_id IS NULL) OR
    Table2.order_id IS NOT NULL

CodePudding user response：

We can use ROW_NUMBER here.

The partition will be done by order_name and we will sort by transaction_id:

WITH sub AS
(SELECT order_id, order_name, transaction_id, 
ROW_NUMBER() 
  OVER (PARTITION BY order_name ORDER BY transaction_id DESC) AS rowNr
FROM Current_Orders_tbl)
SELECT order_id, order_name, transaction_id
FROM sub
WHERE rowNr = 1;

This will - according to your sample - fetch only that row per order_name having the highest transaction_id.

Try out here

Sidenote: If the result of that query should be sorted, I think adding ORDER BY order_id, order_name would be best here.

CodePudding user response：

Try with exists and correlated subquery as the following:

select order_id, order_name, transaction_id 
from table_name T
where 
  ( /* for the 1st requirement: The order_id that contains a transaction_id that matches an already used order_id.*/
    transaction_id is not null and 
    exists(select 1 from table_name D where D.order_id = T.transaction_id)
  )
or
 ( /* for the 2nd requirement: The null transaction_id if that order_id hasn't been used before. */
   transaction_id is null and 
   not exists(select 1 from table_name D where D.transaction_id = T.order_id)
 )

See demo