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

joomla remove category name in url

-) If anyone is using the lyftenbloggie component, and want to remove the component name and category title from the sef url, follow this hack: (Note: You also need to install the lyftenbloggie extension for ARTIO SEF) Under components/com_sef/joomsef.php, After this code: $location = array();         foreach ($title as $titlePart) {             if (strlen($titlePart) == 0) continue;             $location[] = JoomSEF::_titleToLocation($titlePart);         } Just add this code: if($location[0] == 'lyftenbloggie')         {         $temp_sef = end($location);         $location = array();            $location[] = $temp_sef;         ...
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