如何在四个表上使用JOIN在mysql中选择DISTINCT记录[关闭]

I have four tables named college_list, course_list, branch_list, semester_list. And have applied JOIN on these tables as under.

 SELECT DISTINCT college_list.clg_name, course_list.crs_name, branch_list.bran_name, semester_list.sem_name FROM college_list 
       LEFT JOIN course_list ON college_list.clg_code = course_list.crs_clg_code
       LEFT JOIN branch_list ON course_list.crs_code = branch_list.bran_crs_code
       LEFT  JOIN semester_list ON branch_list.bran_crs_code = semester_list.sem_crs_code GROUP BY bran_name
       UNION ALL
       SELECT DISTINCT college_list.clg_name, course_list.crs_name, branch_list.bran_name, semester_list.sem_name FROM college_list 
       RIGHT JOIN course_list ON college_list.clg_code = course_list.crs_clg_code
       LEFT JOIN branch_list ON course_list.crs_code = branch_list.bran_crs_code
       LEFT  JOIN semester_list ON branch_list.bran_crs_code = semester_list.sem_crs_code GROUP BY bran_name
       UNION ALL
       SELECT DISTINCT college_list.clg_name, course_list.crs_name, branch_list.bran_name, semester_list.sem_name FROM college_list 
       RIGHT JOIN course_list ON college_list.clg_code = course_list.crs_clg_code
       RIGHT JOIN branch_list ON course_list.crs_code = branch_list.bran_crs_code
       LEFT  JOIN semester_list ON branch_list.bran_crs_code = semester_list.sem_crs_code GROUP BY bran_name
       UNION ALL
       SELECT DISTINCT college_list.clg_name, course_list.crs_name, branch_list.bran_name, semester_list.sem_name FROM college_list 
       RIGHT JOIN course_list ON college_list.clg_code = course_list.crs_clg_code
       RIGHT JOIN branch_list ON course_list.crs_code = branch_list.bran_crs_code
       RIGHT  JOIN semester_list ON branch_list.bran_crs_code = semester_list.sem_crs_code GROUP BY bran_name

The result of this query is as below.

But I want the college name to come for once. And same with other columns. So how can I use GROUP BY or DISTINCT commands to get so?

Write this code I think your problem will be solved

 SELECT  college_list.clg_name, course_list.crs_name, branch_list.bran_name, semester_list.sem_name FROM college_list 
                    LEFT JOIN course_list ON college_list.clg_code = course_list.crs_clg_code
                    LEFT JOIN branch_list ON course_list.crs_code = branch_list.bran_crs_code
                    LEFT  JOIN semester_list ON branch_list.bran_crs_code = semester_list.sem_crs_code GROUP BY bran_name
                    UNION ALL
                    SELECT DISTINCT college_list.clg_name, course_list.crs_name, branch_list.bran_name, semester_list.sem_name FROM college_list 
                    RIGHT JOIN course_list ON college_list.clg_code = course_list.crs_clg_code
                    LEFT JOIN branch_list ON course_list.crs_code = branch_list.bran_crs_code
                    LEFT  JOIN semester_list ON branch_list.bran_crs_code = semester_list.sem_crs_code GROUP BY bran_name
                    UNION ALL
                    SELECT DISTINCT college_list.clg_name, course_list.crs_name, branch_list.bran_name, semester_list.sem_name FROM college_list 
                    RIGHT JOIN course_list ON college_list.clg_code = course_list.crs_clg_code
                    RIGHT JOIN branch_list ON course_list.crs_code = branch_list.bran_crs_code
                    LEFT  JOIN semester_list ON branch_list.bran_crs_code = semester_list.sem_crs_code GROUP BY bran_name
                    UNION ALL
                    SELECT DISTINCT college_list.clg_name, course_list.crs_name, branch_list.bran_name, semester_list.sem_name FROM college_list 
                    RIGHT JOIN course_list ON college_list.clg_code = course_list.crs_clg_code
                    RIGHT JOIN branch_list ON course_list.crs_code = branch_list.bran_crs_code
                    RIGHT  JOIN semester_list ON branch_list.bran_crs_code = semester_list.sem_crs_code GROUP BY bran_name

                    group by college_list.clg_name

Since I do not have the data from your tables, I am not able to test my solution. I suggest the following query:

SELECT * FROM (SELECT college_list.clg_name, course_list.crs_name, branch_list.bran_name, semester_list.sem_name FROM college_list 
       LEFT JOIN course_list ON college_list.clg_code = course_list.crs_clg_code
       LEFT JOIN branch_list ON course_list.crs_code = branch_list.bran_crs_code
       LEFT  JOIN semester_list ON branch_list.bran_crs_code = semester_list.sem_crs_code 
       UNION ALL
       SELECT DISTINCT college_list.clg_name, course_list.crs_name, branch_list.bran_name, semester_list.sem_name FROM college_list 
       RIGHT JOIN course_list ON college_list.clg_code = course_list.crs_clg_code
       LEFT JOIN branch_list ON course_list.crs_code = branch_list.bran_crs_code
       LEFT  JOIN semester_list ON branch_list.bran_crs_code = semester_list.sem_crs_code 
       UNION ALL
       SELECT DISTINCT college_list.clg_name, course_list.crs_name, branch_list.bran_name, semester_list.sem_name FROM college_list 
       RIGHT JOIN course_list ON college_list.clg_code = course_list.crs_clg_code
       RIGHT JOIN branch_list ON course_list.crs_code = branch_list.bran_crs_code
       LEFT  JOIN semester_list ON branch_list.bran_crs_code = semester_list.sem_crs_code 
       UNION ALL
       SELECT DISTINCT college_list.clg_name, course_list.crs_name, branch_list.bran_name, semester_list.sem_name FROM college_list 
       RIGHT JOIN course_list ON college_list.clg_code = course_list.crs_clg_code
       RIGHT JOIN branch_list ON course_list.crs_code = branch_list.bran_crs_code
       RIGHT  JOIN semester_list ON branch_list.bran_crs_code = semester_list.sem_crs_code) AS TEMP GROUP BY clg_name, crs_name, bran_name, sem_name

You first combine the separate queries, forming a virtual table, then select from this table grouping by all columns. You want to group the whole result set, not separately by each of the 4 queries.