1. Inner join:
Sample Fields in "person" table: id, email, password, firstname, lastname
Sample Fields in "personprofile" table: person_id, age, dob, city, country, phone, mobile
Query:
select * from person inner join `personprofile` on person.id=personprofile.person_id and person.id=9;
2. Left join:
Sample fields in "employee" table: emp_id (primary), name, active
Sample fields in "online" table: id (primary), user_id (references emp_id), online
Query:
SELECT * FROM employee e left join online o on e.emp_id = o.user_id where e.active=1;
Note the use of "where" in the above query, instead of normal "and". It will give the accurate results.
Sample Fields in "person" table: id, email, password, firstname, lastname
Sample Fields in "personprofile" table: person_id, age, dob, city, country, phone, mobile
Query:
select * from person inner join `personprofile` on person.id=personprofile.person_id and person.id=9;
2. Left join:
Sample fields in "employee" table: emp_id (primary), name, active
Sample fields in "online" table: id (primary), user_id (references emp_id), online
Query:
SELECT * FROM employee e left join online o on e.emp_id = o.user_id where e.active=1;
Note the use of "where" in the above query, instead of normal "and". It will give the accurate results.
Hi. I just came across this example. Just in case anybody is looking for more details on MySQL join, specifically about inner joins, maybe take a look at:
ReplyDeletehttp://mysqljoin.com
There are a lot of examples and descriptions for every MySQL supported join.