CODEWITHSUNDEEP
sql
Raj Trivedi
Raj Trivedi

Reputation: 21

Inner join on 2 tables giving wrong output

I have 2 tables

1.Security Table

Security Table
2.Data Table
Data Table
Now User A should only access 1001 and 3001 from DATA1 and DATA2 columns. But as they are not in single row while writing a INNER JOIN it is giving 0 records as my output.

I am looking for an output where user A will be able to see 1001 and 3001 AND user B will get 1001 and 3002 with 10000 and 20000 amount respectively.

Please suggest.

Thanks in advance.

Upvotes: 1

Views: 55

Answers (2)

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY THIS: you can achieve desired output in the following way, I am using temporary table of SQLSERVER so please change is according to your need.

CREATE TABLE #security(SRNO INT IDENTITY(1,1), UNAME VARCHAR(50), DATA1 INT, DATA2 INT)
INSERT INTO #security VALUES 
('A', 1001, NULL),
('A', NULL, 3001),
('B', 1001, NULL),
('B', NULL, 3002)

CREATE TABLE #data(DATA1 INT, DATA2 INT, AMT INT)
INSERT INTO #data VALUES 
(1001,3001,10000),
(1001,3002,20000),
(1002,3001,30000)

SELECT s.UNAME, d.DATA1, d.DATA2, d.AMT
FROM #data d
INNER JOIN (SELECT UNAME, 
        MAX(DATA1) DATA1, 
        MAX(DATA2) DATA2 
    FROM #security GROUP BY UNAME) s ON s.DATA1 = d.DATA1
        AND s.DATA2 = d.DATA2

OUTPUT:

UNAME   DATA1   DATA2   AMT
A       1001    3001    10000
B       1001    3002    20000

Upvotes: 2

India.Rocket
India.Rocket

Reputation: 1235

Try this:-

Select a.UNAME,a.data1,a.data2,b.AMT
from
(
Select UNAME, 
sum(case when data1>=0 then data1 else 0 end) as data1,
sum(case when data2>=0 then data2 else 0 end) as data2
from Security 
group by UNAME
) a
inner join
data b
on a.data1=b.data1 and a.data2=b.data2;

Thanks:-)

Upvotes: 2

Related Questions