CODEWITHSUNDEEP
sqldb2
software_art
software_art

Reputation: 57

sql join between two subqueries that are similar

I am a beginner in SQL. I have two tables, one is student_records and the other is a table student_fees in which the records for students on discounted fees (fee_type_code=2) as well as those on regular fees (fee_type_code=1) exists. the code for fee type is fee_type_code. There is a mistake in system due to which some of the students are getting both fee types. I want to get all the error records. First, i have two queries that work fine separately but i am trying to merge them in one query and then create a join on temporary tables:

select 
student_id, fee_type_code 
from
      ((select * from student_records r, student_fees f 
        where s.student_id=f.student_id and fee_type_code=1) A 
        inner join 
       (select * from student_records r, student_fees f 
        where       
        s.student_id=f.student_id and fee_type_code=2) B 
       on A.student_id=B.student_id);

I apologize for any inconvinience in advance or if this is a question too naive, but i am getting stuck in this. The error i am getting is "Reference to student_id is ambiguous..."

Upvotes: 2

Views: 230

Answers (3)

user2858650
user2858650

Reputation:

As others mentioned earlier, there a few syntactically simpler techniques you could employ to get to this dataset but I wanted to make sure you understood what was wrong with your original query.

To explain

"Reference to student_id is ambiguous..."

You have selected student_id and fee_type_code in your SELECT statement but you have actually referenced these fields twice in your subquery (once in subquery A and once in subquery B). The SQL engine is not intelligent enough to recognize that these represent a duplicate of the same data so it cannot decide which subquery field you want to see. To complete the query as you originally designed it, you will need to explicitly tell the SQL engine which fields you want to see in your SELECT: 

select A.student_id, A.fee_type_code --This can be A or B fields as they represent the
                                    -- same records but a table must be defined
from
  ((select student_id, fee_type_code 
    from student_records r, student_fees f 
    where s.student_id=f.student_id and fee_type_code=1) A 
    inner join 
   (select student_id, fee_type_code 
    from student_records r, student_fees f 
    where s.student_id=f.student_id and fee_type_code=2) B 
       on A.student_id=B.student_id);

Upvotes: 1

jpw
jpw

Reputation: 44881

If your goal is to get a list of students that have both fee types then you can use group by and having instead:

select sr.student_id
from student_records sr
join student_fees sf on sr.student_id = sf.student_id
where sf.fee_type_code in (1,2)
group by sr.student_id
having count(distinct sf.fee_type_code) = 2

Upvotes: 1

Siyual
Siyual

Reputation: 16917

If I understand your problem correctly, you're looking for the students with both fee_type_codes values of 1 and 2.

This should do what you want:

Select  R.student_id
From    Student_records R
Join    Student_fees    F   On  R.Student_id = F.Student_id
Where   F.fee_type_code In (1, 2)
Group By R.student_id
Having Count(Distinct F.fee_type_code) = 2

Upvotes: 0

Related Questions