Select in two MySQL tables

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
id	category
1	PHP-MySQL
2	HTML

links
id	link	visits
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.

<<-Count the number of .. -- MySQL INNER JOIN, LEFT ..->>

Daily Test with Code Example

HTML

CSS

JavaScript

PHP-MySQL

Which tag is used to add lists into <ul> and <ol> elements?

<ul>
 <li>http://coursesweb.net/html/</li>
 <li>http://coursesweb.net/css/</li>
</ul>

Which value of the "display" property creates a block box for the content and ads a bullet marker?

block list-item inline-block

.some_class {
  display: list-item;
}

Which instruction converts a JavaScript object into a JSON string.

JSON.parse() JSON.stringify eval()

var obj = {
 "courses": ["php", "javascript", "ajax"]
};
var jsonstr = JSON.stringify(obj);
alert(jsonstr);    // {"courses":["php","javascript","ajax"]}

Indicate the PHP class used to work with HTML and XML content in PHP.

stdClass PDO DOMDocument

$strhtml = '<body><div id="dv1">CoursesWeb.net</div></body>';
$dochtml = new DOMDocument();
$dochtml->loadHTML($strhtml);
$elm = $dochtml->getElementById("dv1");
echo $elm->nodeValue;    // CoursesWeb.net

Select in two MySQL tables

Php-mysql Course

Two Selects in a Query

Daily Test with Code Example

HTML

CSS

JavaScript

PHP-MySQL

Last accessed pages

Popular pages this month