Mapping table with inner join creates duplicates

userzEfm 0
Viewed 26

I have a program and courses mapping table. One program can have multiple courses. I created a mapping table for program to course table(oneToMany). But when I do inner join I am getting duplicates which is not expected for me. I need to filter based on course like find program with a course name so need to join two table. I have two doubts here

  1. I avoid duplicate with distinct keyboard. I need join for filtering with courses, will it be possible to avoid duplicate with out distinct keyword as distinct may hurt my query performance

  2. Will be it possible to select all courses as semi column separated in one query incase that is causing duplicate.

I am using Java entity(Hibernate) for so oneToMany relationship is building this query out of the box.

QUERY

SELECT SeqNo, pgm_id, start_date, end_date, pgm_description, location, timings
From 
PGM_DETAILS T1
INNER JOIN COURSE_DETAILS T2
ON T1.pgm_id=T2.pgm_id
WHERE T1.pgm_id=110 and T2.course_name in('C bascis','Java Basics');

Desired Result

 SeqNo|pgm_id  |start_date|end_date |pgm_description|location|timings
 ---------------------------------------------------------------------
    1    |   110  | 12-Sep-22|20-Sep-22|My test PGM    | NY     |3-5 PM |C basics;Java Basics;

pgm_details

SeqNo|pgm_id  |start_date|end_date |pgm_description|location|timings
---------------------------------------------------------------------
1    |   110  | 12-Sep-22|20-Sep-22|My test PGM    | NY     |3-5 PM  
2    |   101  | 14-Oct-22|21-Oct-22|My Second      | NJ     |8-9 AM  
3    |   102  | 21-Aug-22|30-Sep-22|My JPGM        | NK     |3-5 PM  
4    |   103  | 08-Dec-22|29-Dec-22|Summer Pj      | CA     |7-8 AM
5    |   104  | 12-Sep-22|20-Sep-22|My Ny PGM      | FE     |3-5 PM  
6    |   105  | 12-Sep-22|20-Sep-22|My FE PGM      | CE     |1-5 PM  
7    |   106  | 12-Sep-22|20-Sep-22|My IN PGM      | NJ     |4-7 PM

course_details

mapping_id | pgm_id  | course_name
-------------------------------------------
  1        | 110     | C basics
  2        | 110     | Java basics
  3        | 110     | python basics
  4        | 110     | Angular basics
  5        | 101     | PERL basics
  6        | 101     | Linux basics
  7        | 101     | Spring basics
  8        | 101     | React basics
  9        | 101     | Javascript basics
  10       | 102     | Windows basics
  11       | 102     | Linux basics
  12       | 103     | Spring basics
  13       | 104     | React basics
  14       | 105     | Javascript basics
hibernate entity-framework sql-server sql
1 Answers
SELECT SeqNo, pgm_id, start_date, end_date, pgm_description, location, timings,
(  
   SELECT GROUP_CONCAT(mapping_id) 
   FROM COURSE_DETAILS T2
   WHERE T1.pgm_id=T2.pgm_id

) AS MappingIds
From PGM_DETAILS T1
Daniel Choi0
Related