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.