How can I select records from two different tables in one MySQL query?
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?
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;
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.
SELECT firstName, lastName FROM Table1, Table2 WHERE (Table1.id = Table2.id)