Find In Progress Order in Main Table & Sub Table-CodePudding

Edited sorry I made some mistake in the questions

I have a main and sub table.

The Main Table as below

 order_id    | status
------------- -------------
 12345       | In Progress
 88862       | In Progress

The Sub Table as below

 main_order_id    | order_id    | status
------------------ ------------- -------------
 12345            | 54x         | In Progress
 12345            | 54d         | In Progress
 88862            | 32x         | Complete
 88862            | 32d         | In Progress

I want the result to display only order_id 12345 by combining the main table and sub table using the status.

CodePudding user response：

select 'order_id ' || order_id  from main_table a 
where a.order_id in
        (select main_order_id from sub_table where status='In Progress')
and a.status='In Progress';

CodePudding user response：

As your question is currently written - it is not very clear what you are trying to achieve, from the logical perspective. I can not understand the actual use case for that need, and you might be missing something in the explanation or what exactly you are trying to achieve, but here what you should do to get what you asked for:

I want the result to display only order_id 12345 by combining the main table and sub table using the status"

select * from main_table, sub_table 
where  main_table.order_id = 12345
and    main_table.order_id = sub_table.order_id
and    main_table.status = sub_table.status

Note that this kind of query in actual systems required having proper indexing on the table.