Display only sub order number that have MOrderNo-CodePudding

I have 2 columns which are MOrderNum and SubOrderNum. MOrderNum is for Master Order No that created after approving Sub Order No. How to only display the MSubNum that have multiple SubOrderNum?

Example

MOrderNum	SubOrderNum
A123	A123-01
A123	A123-02
M111	B001
M222	C001

the data that should be displayed is

MOrderNum	SubOrderNum
A123	A123-01
A123	A123-02

CodePudding user response：

 --it will check if MOrderNum exists in the counting list
SELECT MOrderNum, SubOrderNum
from tbl
WHERE MOrderNum in(
      --It will take all the MOrderNum with their count more than one
      SELECT MOrderNum FROM tbl
      GROUP BY MOrderNum
      HAVING COUNT(MOrderNum)>1
  )
 Order by MOrderNum

Fiddle Demo

CodePudding user response：

You can do this more efficiently with a window function

SELECT MOrderNum, SubOrderNum
FROM (
    SELECT *,
      cnt = COUNT(*) OVER (PARTITION BY MOrderNum)
    FROM tbl
) tbl
WHERE cnt > 1;

CodePudding user response：

this should work as well:

SELECT a.* 
FROM (
    SELECT MOrderNum
    FROM [your table]    
    GROUP BY MOrderNum
    HAVING COUNT(DISTINCT SubOrderNum) > 1  
    ) AS b 
INNER JOIN [your table] AS a              
ON a.MOrderNum = b.MOrderNum
ORDER BY a. MOrderNum