There are two ways of handling this, the long way and the short way.
The long way is to prefix each ambiguously named column with the full table
name, as you have been doing. That *should* have worked for you since that
seems to be what you are doing in your example. Or did you literally write
laptops.field_1 in your query when the column names were
laptops.hard_drive_size and laptops.memory_size or whatever? Remember,
you have to prefix every ambiguously named column in the query with its
table name, even if that ambigous column name occurs in the SELECT clause,
the FROM clause, the WHERE clause, the GROUP BY clause, the HAVING clause,
or the ORDER BY clause. Maybe you removed ambiguities in the WHERE but not
the ORDER BY or some other clause?
The short way, and probably the most widely seen way, is to use a sort of
temporary variable to serve as a substitute for the table name. For example,
given the following tables:
Laptops
=
hard_drive_size
memory_size
CPU
Desktops
==
hard_drive_size
memory_size
CPU
You could write a query like this:
select l.hard_drive_size, d.hard_drive_size, l.memory_size, d.memory_size
from laptops as l, desktops d
where l.CPU = d.CPU
order by l.memory_size;
The query I've just stated doesn't make a whole lot of sense in terms of
what it means but it illustrates how to use the as clause to make a
temporary alias for the table name and how to use these aliases in the rest
of the query.
I think that answers the gist of your question.
I have one qualm about your note though. If you are talking about combining
two tables that have the exact same columns, you aren't really joining them.
The word join has a fairly narrow meaning in SQL. Normally, we join tables
that are different from one another but that have one (or sometimes more)
things in common. I don't know if you've had any data normalization yet -
First, Second, and Third Normal Form - but most joins (in the SQL sense) are
between two tables that have a parent-child relationship with a primary
key/foreign key. For example, one table may list employees with one row per
employee while another table lists departments with one row per department.
If each employee belongs to one and only one department but a department has
potentially many different employees, this is said to be a one-to-many
relationship. Typically, we'd store the employee's department number in the
employee table; the department number would be a primary key in the
department table and a foreign key in the employee table. Then, if you
wanted a report showing departments and the people who work in them, you
would join the department and employee tables on the thing they have in
common, the department number. The two tables would likely have nothing else
in common. That would be a classic example of a join in a relational
database.
The situation where you describe two tables with the same columns is more
likely to be a case where you'd combine the tables via a UNION. Basically,
you would interleave them into a single table. Think of two decks of cards,
one with red backs and one with blue backs. A UNION is the same idea as
shuffling those two decks together. The end result, of course, is that you'd
end up with a single deck of cards, some with red backs and some with blue
backs. That is quite a different situation than joining.
Sorry to go into lecture mode but its a rather important distinction in
relational databases.
Rhino
- Original Message -
From: Brad Tilley [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, April 03, 2004 3:00 PM
Subject: two tables with same field names into one table
Hello,
I am a mysql newbie. Recently, I've been given the task of joining two
tables
within the same DB into one table. Currently, the tables are named
'desktops'
and 'laptops'... ultimately, I would like one table named 'computers' Both
tables have the exact same fields... they fields even have the same names.
I
tried this:
create table computers
select * from desktops, laptops where
desktops.field_1 = laptops.field_1
...
...
...
But I got an error about duplicate field names. Any suggestions on how to
do
this?
Thanks,
Brad
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
