CODEWITHSUNDEEP
sqlsql-servervb.netvisual-studio-2010
Tabish Aziz
Tabish Aziz

Reputation: 13

There are two tables. i want to join them in such a way that i can get the following result

student_mas::                  receipt_mas
name    class                   name     class     month
john     2nd                   john       2nd       JAN
bunny    3rd                   john       2nd       FEB
sunny    4th                   bunny      3rd       FEB

student who submits fees for a particular month gets inserted into the second table mentioning the month in the month column in the second table I want the list of students who have not submitted the fees for the month of JAN please help me. thanks in advance.

Upvotes: 1

Views: 77

Answers (3)

LalitStars
LalitStars

Reputation: 1

with student_mas as
(
SELECT 'JOHN' NAME,'2ND' CLASS FROM DUAL
union all
SELECT 'BUNNY' NAME,'3RD' CLASS FROM DUAL
union all
SELECT 'SUNNY' NAME,'4TH' CLASS FROM DUAL
)
select * from student_mas A
where not exists
(
    with receipt_mas as
    (
    SELECT 'JOHN' NAME,'2ND' CLASS,'JAN' MONTH FROM DUAL
    union all
    SELECT 'BUNNY' NAME,'3RD' CLASS,'FEB' MONTH FROM DUAL
    union all
    SELECT 'SUNNY' NAME,'4TH' CLASS,'FEB' MONTH FROM DUAL
    )
    select * from receipt_mas B
    where  A.NAME=B.NAME
    and    A.CLASS=B.CLASS
    and    B.MONTH='JAN'
)
/

Upvotes: 0

StackUser
StackUser

Reputation: 5398

Ullas answer would work perfectly but you can try like the below approach. 

DECLARE @student_mas TABLE (
    NAME VARCHAR(50)
    ,class VARCHAR(10)
    );

insert into @student_mas 
values
('john', '2nd'),
('bunny', '3rd'),
('sunny', '4th');

DECLARE @receipt_mas TABLE (
    NAME VARCHAR(50)
    ,class VARCHAR(10)
    ,[month] VARCHAR(3)
    );

insert into @receipt_mas
values
('john', '2nd', 'JAN'),
('john', '2nd', 'FEB'),
('bunny', '3rd', 'FEB');

SELECT sm.*
FROM @student_mas sm
LEFT JOIN @receipt_mas rm ON sm.NAME = rm.NAME
    AND sm.class = rm.class
    AND rm.month = 'JAN'
WHERE RM.class IS NULL

Upvotes: 1

Ullas
Ullas

Reputation: 11556

You can use NOT EXISTS

Query

select * from student_mas t
where not exists (
    select * from receipt_mas 
    where name = t.name
    and class = t.class
    and [month] = 'JAN'
);

SQL Fiddle demo

Upvotes: 1

Related Questions