How can I select records from two different tables in one MySQL query?

Question

I have first names stored in one table and last names stored in another. I know this is silly but I am trying out different things as I'm just starting with MySQL. Anyway is it possible to select the first name from one table and the last name from another in one query? And put the result inside a PHP variable?

Answer 1

You must have something that binds the two tables together, that is a common key. Something like Id in the example below:

Table 1

Id Fname
--------
1 Roger
2 Pete

Table 2

Id Lname
--------
1 Federer
2 Sampras

In that case you can get the full name as:

SELECT Fname, Lname from T1,T2 where T1.Id = T2.Id;

Answer 2

Use joins

SELECT firstName, lastName  
FROM Table1, Table2 
WHERE (Table1.id = Table2.id)

Answer 3

select table1.firstname, table2.lastname from table1, table2
    where table1.id = table2.id

See here for more information.

The Full Join

If a SELECT statement names multiple tables in the FROM clause with the names separated by commas, MySQL performs a full join.

Answer 4

You should read about Database normalization, which will help you design your tables like this.