AVI AVI - 1 month ago 10
SQL Question

Retrieving data by using joins

How to get inner join data by using outer join?

TABLE A

ID SALES
-----------
1 100
2 200
3 300
4 400


TABLE B:

ID SALES
-----------
1 100
2 200


BY USING LEFT JOIN I WANT ONLY MATCHING 2 RECORDS

Desired output:

ID SALES ID SALES
--------------------
1 100 1 100
2 200 2 200

Answer
CREATE TABLE #A                             
(ID INT   , SALES     INT)
INSERT INTO     #A VALUES                       
(1,100),                                  
(2,200),                                  
(3,300),
(4,400)

CREATE TABLE #B 
(
ID INT,SALES INT
)
INSERT INTO #B VALUES
(1,100),
(2,200)




SELECT * FROM #A  A  LEFT JOIN 
#B B ON A.ID=B.ID
AND A.SALES=B.SALES
WHERE B.ID IS NOT NULL

OUTPUT

ID  SALES  ID  SALES
1   100     1  100
2   200     2  200