-1

I have tables like this:

people

id_p    name      email         phone
------------------------------------------
1       Wanda     a@a.a     +123
2       Vision    b@b.b     +234
3       Falcon    c@c.c     +345
4       Winter S  d@d.d     +456

lecturers

id_a    id_p     department     field          id_institution
-------------------------------------------------------------
1       1        Enginering     Electronics    4
2       2        Life Science   Psychology     12

students

id_b    id_p       major        field      id_institution
-------------------------------------------------------
1       3          Computer     Robotic    1
2       4          Computer     AI         5

I want to JOIN this to table to another table, this is query to get student:

SELECT people.* , student.major, student.field, another_table.some, another_table2.some FROM people
LEFT JOIN student ON student.id_p = people.id_p
LEFT JOIN another_table ON another_table.major = student.major
LEFT JOIN another_table2 ON another_table2.id_institution = student.id_institution

I wanna get the lecturer to, this is my query:

SELECT people.* , lecturer.department, lecturer.field, another_table.some, another_table2.some FROM people
LEFT JOIN lecturer ON lecturer.id_p = people.id_p
LEFT JOIN another_table ON another_table.departmen = lecturer.department
LEFT JOIN another_table2 ON another_table2.id_institution = lecturer.id_institution

These 2 queries is similiar. My real query is more complex than these queries, and have sub-queries. I use DataTable server-side because there is more than 10000 data to processed. But, even in server-side, it still take long time, and I think it is happend because there is two complex queries to be executed.

How to merge these 2 smiliar queries?

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
Ilya Trianggela
  • 305
  • 1
  • 9
  • 1
    What relationship do lecturers have to students, in this context? "It looks similar" or "They both have 3 string columns" is not really sufficient justification to combine queries – Caius Jard Mar 08 '21 at 12:32
  • Is there perhaps a `class` table and/or a `class_enrollment` table? As it is there doesn't appear a way to related students to lecturers in any meaningful way. – Bob Jarvis - Слава Україні Mar 08 '21 at 12:36
  • @CaiusJard There is no relationship of lecturers and students. I just wanna get all people data, but it have to use 2 queries because there is different collumn name in student and lecturer. I asking is there is possible to di it? – Ilya Trianggela Mar 08 '21 at 12:45
  • [Anything is possible](https://imgflip.com/i/2caj3o) – Caius Jard Mar 08 '21 at 13:23

1 Answers1

0

You can do that using union all

SELECT people.* , student.major, student.field, another_table.some, another_table2.some FROM people
LEFT JOIN student ON student.id_p = people.id_p
LEFT JOIN another_table ON another_table.major = student.major
LEFT JOIN another_table2 ON another_table2.id_institution = student.id_institution

union all 

SELECT people.* , lecturer.department, lecturer.field, another_table.some, another_table2.some FROM people
LEFT JOIN lecturer ON lecturer.id_p = people.id_p
LEFT JOIN another_table ON another_table.departmen = lecturer.department
LEFT JOIN another_table2 ON another_table2.id_institution = lecturer.id_institution

But you will no longer see the difference between a lecturer and a student, so you might need to add this column:

SELECT 'student' as fromtable, people.* , student.major, student.field, another_table.some, another_table2.some FROM people
LEFT JOIN student ON student.id_p = people.id_p
LEFT JOIN another_table ON another_table.major = student.major
LEFT JOIN another_table2 ON another_table2.id_institution = student.id_institution

union all 

SELECT 'lecturer', people.* , lecturer.department, lecturer.field, another_table.some, another_table2.some FROM people
LEFT JOIN lecturer ON lecturer.id_p = people.id_p
LEFT JOIN another_table ON another_table.departmen = lecturer.department
LEFT JOIN another_table2 ON another_table2.id_institution = lecturer.id_institution
Caius Jard
  • 72,509
  • 5
  • 49
  • 80
Luuk
  • 12,245
  • 5
  • 22
  • 33
  • Thanks for the edit, i finally search for the way to do it, and found it [When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/a/11321508/724039), where it says: "Double quotes are supported by MySQL for string values as well, but single quotes are more widely accepted by other RDBMS, so it is a good habit to use single quotes instead of double." – Luuk Mar 08 '21 at 13:29