CODEWITHSUNDEEP
sql-server-2008
user1903388
user1903388

Reputation: 21

sql query for joining 3 tables

table A: userid name

table B: accid userid accno

table c: loanid userid amt

Hi frnds, I want to take name of customer from table A, accno from table B and they doesn't include in table c. help me

Upvotes: 2

Views: 445

Answers (3)

Pankaj Dubey
Pankaj Dubey

Reputation: 824

SELECT a.name, b.accno
FROM a inner join b on b.userid = a.userid
where a.userid not in (select distinct userid from c)

what about this Query this will work fine i think

Upvotes: 2

Niladri Biswas
Niladri Biswas

Reputation: 4171

Solution 1:

;with cte as
(Select a.name,b.accno,a.userid
from tableA a
join tableB b
on a.userid = b.userid)

select x.name,x.accno
from cte x 
where x.userid not in (select userid from tableC)

Solution 2:

;with cte as
(Select a.name,b.accno,a.userid
from tableA a join tableB b 
on a.userid = b.userid)
select x.name,x.accno
from cte x 
where x.userid in(select c.userid 
                 from cte c 
                 except 
                 select tc.userid 
                from tableC tc)

Solution 3

;with cte as
(Select a.name,b.accno,a.userid
from tableA a join tableB b 
on a.userid = b.userid)

select x.name,x.accno
from cte x
left join tableC tc
on x.userid = tc.userid
where tc.userid is null

Upvotes: 1

cjk
cjk

Reputation: 46425

Try:

SELECT a.name, b.accno
FROM tableA a 
JOIN tableB b on a.userid = b.userid
LEFT JOIN tableC c on a.userid = c.userid
WHERE c.userid IS NULL

Doing a left join and checking that the values returned are null, checks that there isn't a record in table c for records in tables a and b.

Upvotes: 1

Related Questions