select from two different tables in single query using mysql

Question

I have two different tables. One is instructor and second is student. Both table structure are as follows:

________INSTRUCTOR_________

instructorid schoolid instructorusername instructorfirstname instructorlastname instructortitle level
1             1        inst_A              first_A             Last_A              RN            3
2             1        inst_B              first_B             Last_B              TD            3
3             1        inst_C              first_C             Last_C              FP            3

________STUDENT_________

studentid   schoolid  studentusername    studentfirstname    studentlastname    level
1             1        stud_A              first_A             Last_A            4
2             1        stud_B              first_B             Last_B            4
3             1        stud_C              first_C             Last_C            4

Now, I want to select data from both tables where schoolid is '1' and the result will be like Bellow :

________RESULT_________

id schoolid username firstname lastname title level
1   1        inst_A   first_A   Last_A   RN    3
2   1        inst_B   first_B   Last_B   TD    3
3   1        inst_C   first_C   Last_C   FP    3
1   1        stud_A   first_A   Last_A         4
2   1        stud_B   first_B   Last_B         4
3   1        stud_C   first_C   Last_C         4

Is there any way to do like this? Please can any one help me for this. I have never done like this before.

Thank you in Advance

John Woo · Accepted Answer · 2013-01-30T13:58:19.080

4

use UNION ALL (actually just UNION will do since students have no title). SInce table STUDENT doesn't have title, you need to have an empty column with alias title so it will match the column count. If you don't add extra column for title, you will have error message

The used SELECT statements have a different number of columns...

Full Query,

SELECT  schoolid, 
        instructorusername username, 
        instructorfirstname firstName, 
        instructorlastname lastName,
        instructortitle title,
        level
FROM    instructors
UNION 
SELECT  schoolid, 
        studentusername username, 
        studentfirstname firstName, 
        studentlastname lastName,
        '' title,
        level
FROM    students

SQLFiddle Demo

edited Jan 30 '13 at 13:58

answered Jan 30 '13 at 13:52

John Woo

258,903
69
498
492

Hello JW, How can I apply LIMIT to this query? – Hardik Jan 30 '13 at 14:10
what do you mean by limit? limit all the records? by what? but to answer your question directly, `SELECT schoolid, instructorusername username, instructorfirstname firstName, instructorlastname lastName, instructortitle title, level FROM instructors UNION SELECT schoolid, studentusername username, studentfirstname firstName, studentlastname lastName, '' title, level FROM students LIMIT 10` will show 10 records. – John Woo Jan 30 '13 at 14:11
I mean that I have many more entries in both tables above 500. Now I want to show my result in a data-table grid and apply pagination in it. So when every user go to another page I want to apply LIMIT when fetching result from database – Hardik Jan 30 '13 at 14:15
oh i see, here's a greate article on [Pagination of MySQL Query Results](http://php.about.com/od/phpwithmysql/ss/php_pagination.htm) – John Woo Jan 30 '13 at 14:17
Will this Limit apply to student table or whole result set? – Hardik Jan 30 '13 at 14:18

score 2 · Answer 2 · answered Jan 30 '13 at 13:52

2

UNION is the key here:

select schoolid, instructorusername as username,  instructorfirstname as firstname, instructorlastname as lastname, instructortitle as title
from INSTRUCTOR
UNION
select schoolid, studentusername as username,  studentfirstname as firstname, studentlastname as lastname, "" as title
from STUDENT

answered Jan 30 '13 at 13:52

Kris

14,426
7
55
65

Hello Kris, How can I apply LIMIT to this query? – Hardik Jan 30 '13 at 14:09
@Hardik the LIMIT can be placed at the end to limit the overall number or you can do something like [this](http://stackoverflow.com/a/1415380/2773160) by putting each query in parenthesis. – p1l0t Feb 10 '14 at 14:45

select from two different tables in single query using mysql

2 Answers2