In this tutorial you can learn how to select data from two MySQL tables with a single query.
When the SQL query selects columns from diferent tables, you must specify the table name and the column; the syntax is:
SELECT `table1`.`column`, `table2`.`column` FROM `table1`, `table2` WHERE conditon

Let's see some examples, in which we'll use the following two tables, named "categories" and "links".
categories
idcategory
1 PHP-MySQL
2 HTML
links
idlinkvisits
1 coursesweb.net/php-mysql/arrays 12
1 coursesweb.net/php-mysql/strings 15
2 coursesweb.net/html/html-tables 18

1. Select all the columns from "categories" table where id=2, and the "link" and "visits" columns where visits>13.
SELECT `categories`.*, `links`.`link`, `links`.`visits` FROM `categories`, `links` WHERE `categories`.`id`=2 AND `links`.`visits`>13
Result:
| id | category |                link             | visits |
-----------------------------------------------------------------
| 2 |   HTML    | coursesweb.net/php-mysql/strings |   15   |
| 2 |   HTML    | coursesweb.net/html/html-tables  |   18   |


2. Select the "category", and the "link" columns, where the 'id' in the 'categories' table is 1 and the id in the 'links' table is equal with the 'id' of the 'categories'.
SELECT `categories`.`category`, `links`.`link` FROM `categories`, `links` WHERE `categories`.`id`=1 AND `categories`.`id`=`links`.`id`
Result:
| category  |               link                   |
----------------------------------------------------
| PHP-MySQL | coursesweb.net/php-mysql/arrays  |
| PHP-MySQL | coursesweb.net/php-mysql/strings |

Two Selects in a Query

You can also perform two select commands in a SQL query.
Examples:

1. Select the "link" column (from 'links') where the "id" corresponds to the HTML value (stored in the 'categories').
SELECT `link` FROM `links` WHERE `id`=(SELECT `id` FROM `categories` WHERE `category`='HTML')
Result:
|               link                  |
---------------------------------------
| coursesweb.net/html/html-tables |


2. Counts all rows in the 'links' table, where visits>14, and selects "category" column from 'categories' where id<4.
SELECT (SELECT COUNT(*) FROM `links` WHERE `visits`>14) AS nrl, `category` FROM `categories` WHERE `id`<4
Result:
| nrl | category  |
-------------------
|  2  | PHP-MySQL |
|  2  |   HTML    |

- There is also another way to select columns from two tables in a single query, by using the JOIN command. This method is presented in the next tutorial.

Daily Test with Code Example

HTML
CSS
JavaScript
PHP-MySQL
Click on the HTML tag which creates an horizontal line in web page.
<br /> <em> <hr />
Some content ...
<hr />
Content under line ...
Which CSS property defines the text color?
font-style font-variant color
h2 {
  color: #cbdafb;
}
Click on the function which searches if a character, or text exists in a string.
indexOf() toString() split()
var str = "Web courses - http://CoursesWeb.net/";
if(str.indexOf("http://") == -1) alert("http:// isn`t in string");
else alert("http:// is in string");
Which function splits a string into an array of strings based on a separator?
array_merge() explode() implode()
$str = "apple,banana,melon,pear";
$arr = explode(",", $str);
var_export($arr);      // array (0=>"apple", 1=>"banana", 2=>"melon", 3=>"pear")
Select in two MySQL tables

Last accessed pages

  1. Using file_put_contents, file_get_contents, readfile and file() (5435)
  2. Bubbles3 (47680)
  3. Functions with Object and Array arguments (2091)
  4. Add and Remove HTML elements and Content with jQuery (21399)
  5. HTML object and param (8061)

Popular pages this month

  1. Qwop (3462)
  2. Drag Racer V3 (1707)
  3. Flash Games - Free online Games (810)
  4. Bubbles3 (810)
  5. Courses Web: PHP-MySQL JavaScript Ajax HTML CSS Flash-AS3 (758)