duplicate columns when using join

Question

I am trying to join two tables: trainSearchStream and SearchInfo based on a common column: SearchID.

SELECT *
FROM   trainSearchStream
       INNER JOIN SearchInfo
               ON trainSearchStream.SearchID = SearchInfo.SearchID
LIMIT  3;

The problem is that the result has two columns for SearchID. How can I display only one column for SearchID?

These are the columns of trainSearchStream:

SearchID,AdID,Position,ObjectType,HistCTR,IsClick

and these are columns for SearchInfo:

 SearchDate,IPID,UserID,IsUserLoggedOn,SearchQuery,LocationID,CategoryID,SearchParams

My other question is that how can I save the result of my select statement in a new table?

@MAS In the future, please only tag the database software you are using. — Colonel Thirty Two, Jul 08 '15 at 16:44

spencer7593 · Accepted Answer · 2015-07-08T14:31:52.660

Ditch the * in the SELECT list and give a specific list of columns to return.

  SELECT t.SearchID
       , t.AdID
       , t.Position
       , t.ObjectType
       , t.HistCTR
       , t.IsClick
       , i.SearchDate
       , i.IPID
       , i.UserID
       , i.IsUserLoggedOn
       , i.SearchQuery
       , i.LocationID
       , i.CategoryID
       , i.SearchParams
    FROM trainSearchStream t
    JOIN SearchInfo i 
      ON t.SearchID = i..SearchID
   LIMIT 3;

To "save" the result from that in another table, you can use an INSERT ... SELECT statemeent. If you want to create a new table, you can use a CREATE TABLE ... SELECT statement.

References:

INSERT ... SELECT https://dev.mysql.com/doc/refman/5.5/en/insert-select.html

CREATE TABLE ... SELECT https://dev.mysql.com/doc/refman/5.5/en/create-table-select.html

Also note: best practice is to qualify *all* column references with the table name (or an a table alias), as shown in the example query. This avoids your statement from throwing an "ambiguous column reference" error in the future, if someone were to add a column to a table, e.g. adding `UserID` column to the `trainSearchStream` table. With column references qualified, the statement will continue to work. If a reference to `UserID` is not qualified, then the statement that was working will start throwing an error. — spencer7593, Jul 08 '15 at 14:43

Carsten Massmann · Answer 2 · 2015-07-08T14:31:47.833

1

Specify exactly the columns you want to see. This always the preferred way

SELECT 
t.SearchID,AdID,Position,ObjectType,HistCTR,IsClick,
SearchDate, IPID,UserID,IsUserLoggedOn,SearchQuery,LocationID,CategoryID,SearchParams
FROM trainSearchStream  t
INNER JOIN SearchInfo ON trainSearchStream.SearchID = SearchInfo.SearchID LIMIT 3;

Edit

I forgot to include the alias name and apply that to column SearchId.

edited Jul 08 '15 at 14:31

answered Jul 08 '15 at 14:27

Carsten Massmann

26,510
2
22
43

I get Error: ambiguous column name: SearchID – MAS Jul 08 '15 at 14:30

duplicate columns when using join

2 Answers2