Techie Zone

Complex queries involves fetching data from more than one table, this can be achieved by joining the tables. In this article we will learn how we can fetch data from multiple table using cross joins.

NOTE:
To read data from Multiple Table you need to have Primary Key – Foreign Key Relationship between tables.

Suppose i want to store employee details in the database so i will create an employee table

		CREATE TABLE employee(
		      id int PRIMARY KEY NOT NULL AUTO_INCREMENT, 
		      name varchar(100), 
		      designation varchar(100)
		);

Now i will insert some record inside employee table

   INSERT INTO employee(name, designation) VALUES('ABC','Software Engineer');
   INSERT INTO employee(name, designation) VALUES('PQR','Software Engineer');

Now my employee table holds following data:

Now i want to store employee technical skill set, but i cannot store it in the employee table as employee may have multiple skill sets. So i will now create an new table called skillset.

   		CREATE TABLE skillset(
		      id int PRIMARY KEY NOT NULL AUTO_INCREMENT, 
		      empId int NOT NULL,
		      skills varchar(100), 
              FOREIGN KEY (empid) REFERENCES employee(id) ON DELETE CASCADE ON UPDATE CASCADE
		);

Now i will add some skill sets to the skillset table

   INSERT INTO skillset(empid, skills) VALUES('1','MySql');
   INSERT INTO skillset(empid, skills) VALUES('1','PHP');
   INSERT INTO skillset(empid, skills) VALUES('1','Java');
   INSERT INTO skillset(empid, skills) VALUES('2','Asp.Net');

Now in one query i need the employee skillset for Employee Name: ABC, this can be acheive by joining both the tables.

Regular Expression in MySQL

     SELECT employee.id, skillset.skills FROM employee, skillset WHERE employee.name = 'ABC' AND employee.id = skillset.empid

The above query will return following output:

Now i have a new requirement for finding all employee who have worked on PHP. This can be acheived by executing:

     SELECT employee.id, employee.name FROM employee, skillset WHERE skillset.skills = "Asp.Net" AND employee.id = skillset.empid

The above query will return following output:

The query here are simple as it involves 2 tables but it can become complicated with getting data from 5 to 6 tables, but for that cross join is not suitable option because of the performance for that other Joins are used namely. LEFT JOIN, RIGHT JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN etc

Custom Search

Popular Articles:

• Database Class in PHP5
• MySql Batch Insert/Update in Java
• MySql Batch Insert Using PHP
• BackUp and Restore Mysql Database
• MySql – Create Foreign Key
• MySql – Working With Stored Procedure
• Understanding MySQL Joins
• Installing mytop on Linux
• MySql Prepared Statement in PHP
• Column count doesn’t match value count in MySql