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.
Select in two MySQL tables

Daily Test with Code Example

HTML
CSS
JavaScript
PHP-MySQL
Which attribute is used in <a> tag for the address of the link?
src href rel
<a href="http://coursesweb.net/" title="CoursesWeb.net">CoursesWeb.net</a>
Which CSS property sets the type of the text font?
font-family text-decoration font-size
h2 {
  font-family:"Calibri",sans-serif;
}
What instruction selects all the <div> tags with class="cls"?
querySelector("div.cls") getElementsByTagName("div") querySelectorAll("div.cls")
var elm_list = document.querySelectorAll("div.cls");
var nr_elms = elm_list.length;       // number of selected items
alert(nr_elms);
Indicate the function that can be used to get the sum of values in an array.
array_sum() array_diff() array_shift()
$arr = array(1, 2, 3, 4);
$arr_sum = array_sum($arr);
echo $arr_sum;       // 10

Last accessed pages

  1. CSS - Cascading Style Sheets in HTML (602)
  2. Check if table exists in databse (2335)
  3. Read Excel file data in PHP - PhpExcelReader (27430)
  4. New Form elements and attributes in HTML5 (3136)
  5. Forms and Input (2819)

Top accessed pages

  1. Courses Web: PHP-MySQL JavaScript Ajax HTML CSS Flash-AS3 (35640)
  2. PHP-MySQL free course, online tutorials PHP MySQL code (28504)
  3. Read Excel file data in PHP - PhpExcelReader (27430)
  4. Get Attribute (ID, Class, Name, Title, Src) with jQuery (26626)
  5. PHP PDO - exec (INSERT, UPDATE, DELETE) MySQL (23377)