Skip to main content

Mysql join examples

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.
Labels:

Comments

  1. Jan BrinkmannSeptember 15, 2011 at 7:24 AM

    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:

    http://mysqljoin.com

    There are a lot of examples and descriptions for every MySQL supported join.

    ReplyDelete

Post a Comment

Popular posts from this blog

php strip all tags

The below function can strip almost all tags from a string. function strip_all_tags($string) {     $string = preg_replace( '@<(script|style)[^>]*?>.*?@si', '', $string );     $string = strip_tags($string);     return trim($string); } $a = '<script type="text/javascript" src="jquery.js"></script> <div id="test" style="padding:5px; color:red;">Hello world</div>'; echo strip_all_tags($a); // outputs: Hello world
Post a Comment
Read more

htaccess disable directory browsing, disable file listing, disable file access

There may be some personal or secure data in a file, that you want to hide from the end user, when it is viewed through a web browser. Ex: http://www.example.com/user_files/24/chat.txt In the above scenario, if the file "chat.txt" contains a secure data, and you dont want to allow end users to directly access the file, or to disable the file listing in the folder "user_files" or "24", use the below code in your .htaccess file inside "user_files" folder: Options -Indexes order allow,deny deny from all
Post a Comment
Read more

css link remove dotted outline

Its quite awkward to look at the link, when it is clicked and the dotted outline appears on it. To overcome it, just add the following line to your css file: a { outline: 0; } For example, to remove the dotted outline for the menu links on your page, add this to your css: #navigation li a {    outline: 0; } ( Assuming you have this menu structure: <ul id="navigation"><li><a href="news.html">News</a></li></ul> )
Post a Comment
Read more