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]
