Select all columns from two tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
SmokyBarnable
Forum Contributor
Posts: 105
Joined: Wed Nov 01, 2006 5:44 pm

Select all columns from two tables

Post by SmokyBarnable »

Is there an easy way to select all columns from one table and just one column from a different table?
Also, what happens when you select * from two tables that have column names that are identical?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

You specifiy which tables you want to use using the FROM clause, or by performing a JOIN. If two columns have identical names, one of the values is overwritten (you'll have to look up which one because I have forgotten), therefor if you want to keep both column names you'll need to use an alias

Code: Select all

SELECT table1.id AS id1, table2.id AS id2  
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Select all columns from two tables

Post by califdon »

SmokyBarnable wrote:Is there an easy way to select all columns from one table and just one column from a different table?
Also, what happens when you select * from two tables that have column names that are identical?
Better be careful here or you'll end up with a Cartesian Join, joining every row in one table with every row in the other table, almost certainly not what you want. But if you understand that, the syntax is quite simple:

Code: Select all

SELECT a.*, b.somecol FROM table1 AS a, table2 AS b WHERE a.foreignkey = b.primarykey
Post Reply