SQL query 'Join'-CodePudding

TABLE A >>

ItemNo  Name
1       Item1
2       Item2
3       Item3
4       Item4

TABLE B >>

ItemNo  Status
1       available
1       onhold
1       Sold
1       Transit
------------------
2       available
2       onhold
2       Sold
-----------------
3       Transit
-----------------
4       onhold

There are four status on table B onhold,available,sold,Transit

Expected result: if anyone of the itemNo in table B has on either available or Transit, then I need to get only the table A record of that ItemNo

Output:

Table A:

ItemNo  Name            
1       Item1       
2       Item2
3       Item3

Please advise on how to write MS SQL query for this. Many thanks in advance!

CodePudding user response：

You can solve with a correlated subquery:

SELECT * 
FROM tableA ta 
WHERE ItemNo IN 
    (
        SELECT ItemNo 
        FROM tableB 
        WHERE Status in ('available','Transit') 
          AND ItemNo = ta.ItemNo
    )

CodePudding user response：

Join the tables and use SELECT DISTINCT to get just one row from table A.

SELECT DISTINCT a.*
FROM TableA AS a
JOIN TableB AS b ON a.itemNo = b.itemNo
WHERE b.status in ('available', 'Transit')