I know it’s simple query but I wanted to know how to get Employee’s working under manager from another database table.
Post your DB Schema and we can tell you.
DB1 contains Employee_db: id, emp_id,frist_name,last_name, manager_id,dept_id and so on.
DB2 contains Users: id, emp_id, name, status and so on. In this DB2 I have Leaves and Trips tables, I need to write query to get all leaves and trips data for those employees working under manager.
What I meant was an SQL dump of your database so I could run it.
CREATE TABLE employee_db (
sl_no int(11) NOT NULL AUTO_INCREMENT,
emp_id varchar(10) NOT NULL,
last_name varchar(35) DEFAULT NULL,
first_name varchar(35) DEFAULT NULL,
email varchar(100) DEFAULT NULL,
manager_id varchar(45) DEFAULT NULL,
dept_id varchar(45) DEFAULT NULL,
PRIMARY KEY (sl_no),
KEY dept_id (dept_id),
KEY emp_id (emp_id),
KEY manager_id (manager_id),
CONSTRAINT employee_db_ibfk_1 FOREIGN KEY (dept_id) REFERENCES department_db (dept_id),
CONSTRAINT employee_db_ibfk_2 FOREIGN KEY (manager_id) REFERENCES employee_main_db (emp_id)
) ENGINE=InnoDB AUTO_INCREMENT=1113 DEFAULT CHARSET=latin1
CREATE TABLE leaves (
id int(11) NOT NULL AUTO_INCREMENT,
leave_id varchar(45) NOT NULL,
emp_id varchar(45) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
reason varchar(45) DEFAULT NULL,
status varchar(15) DEFAULT ‘0’,
created_at timestamp NULL DEFAULT NULL,
updated_at timestamp NULL DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
CREATE TABLE trips (
id int(11) NOT NULL AUTO_INCREMENT,
trip_id varchar(45) NOT NULL,
emp_id varchar(45) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
duration varchar(45) NOT NULL,
reason varchar(45) DEFAULT NULL,
status varchar(15) DEFAULT ‘0’,
destination varchar(45) DEFAULT NULL,
created_at timestamp NULL DEFAULT NULL,
updated_at timestamp NULL DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
CREATE TABLE users (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
admin tinyint(1) NOT NULL DEFAULT ‘0’,
emp_id varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
email varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
email_verified_at timestamp NULL DEFAULT NULL,
password varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
remember_token varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
created_at timestamp NULL DEFAULT NULL,
updated_at timestamp NULL DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
DB1 contains tables: users,leaves and trips.
DB2 contains table: employee_db
Are the database’s on the same server, or different systems entirely?
And are they actually two separate Databases? If so, why?
Yes the databases are on the same server itself.
As per the requirement I am doing. Instead of creating everytime new tables for users containing their data we can refer only one table for login and use it in every tool.
If they are on the same server, then you just do a normal join with the databases names.