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".
1 12
1 15
2 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
| id | category |	              link             | visits |
| 2 |   HTML    | |   15   |
| 2 |   HTML    |  |   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`
| category  |               link                   |
| PHP-MySQL |  |
| PHP-MySQL | |

Two Selects in a Query

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

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')
|               link                  |
| |

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
| 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

Which attribute specifies the URL address where to send the form-data?
method action name
<form action="script.php" method="post"> ... </form>
Which CSS property can be used to break lines in the middle of words?
word-wrap line-height font-size
#id {
  width: 100px;
  word-wrap: break-word;
Which function sorts the elements of an array into alphabetical order, based on the string values?
pop() sort() shift()
var tutorials = ["php", "html", "css", "flash"];
alert(tutorials[0]);          // css
Indicate the function that returns the value of the last element into an array.
current() next() end()
$code = array(10=>"Perl", 20=>"PHP", 21=>"Python", 30=>"JavaScript");
$last = end($code);
echo $last;      // JavaScript
Select in two MySQL tables

Last accessed pages

  1. innerHTML and outerHTML to Get and Replace HTML content (1476)
  2. Common PHP Errors and Solutions (1258)
  3. Get Attribute (ID, Class, Name, Title, Src) with jQuery (7120)
  4. PHP PDO - exec (INSERT, UPDATE, DELETE) MySQL (6524)
  5. Circle and Oval with CSS (776)

Top accessed pages

  1. PHP-MySQL free course, online tutorials PHP MySQL code (7627)
  2. Courses Web: PHP-MySQL JavaScript Ajax HTML CSS Flash-AS3 (7402)
  3. Get Attribute (ID, Class, Name, Title, Src) with jQuery (7120)
  4. PHP Chat Script (6566)
  5. PHP PDO - exec (INSERT, UPDATE, DELETE) MySQL (6524)