CODEWITHSUNDEEP
mysqlsqldatabasesqlite
user2731263
user2731263

Reputation: 55

Combine 2 tables into 1

How would I combine 2 tables into 1? Maybe using INNER JOIN? Please advise. Thank you.

For example, with the following db I just created:

Table 1

  +----------------------+
  | Places               |
  +----------------------+
  | Cupertino            |
  | BJ's Restaurant      |
  | Cupertino Inn        |
  | Outback Steakhouse   |
  +----------------------+

Table 2

  +----------------------+
  | Distant              |
  +----------------------+
  | 0.6 km               |
  | 1.3 km               |
  | 1.3 km               |
  | 1.1 km               |
  +----------------------+

Select syntax and combine this table output would be something like this:

+---------------------+---------+
| Places              | Distant |
+---------------------+---------+
| Cupertino           | 0.6 km  |
| BJ's Restaurant     | 1.3 km  |
| Cupertino Inn       | 1.3 km  |
| Outback Steakhouse  | 1.1 km  |
+---------------------+---------+

**Edit: Sorry forgot to add id. Updated below:

Table 1

+-----------+--------------------+
|Places_Id  | Places             |
+-----------+--------------------+
| 1         | Cupertino          |
| 2         | BJs Restaurant     |
| 3         | Cupertino Inn      |
| 4         | Outback Steakhouse |
+-----------+--------------------+

Table 2

+------------+----------+
|Distant_Id  | Distant  |
+------------+----------+
| 1          | 0.6 km   |
| 2          | 1.3 km   |
| 3          | 1.3 km   |
| 4          | 1.1 km   |
+------------+----------+

Places_Id and Distant_Id is Auto Increment. So sorry guys, suddenly my brain stop working after long time not doing programming related. Turns out this is so easy after add the id into database. Thanks guys for all the answers. Keep it up.

Select A.Places, A.Places_Id, B.Distant, B.Distant_Id from Table1 A, Table2 B where B.Distant_Id = A.Places_Id

Upvotes: 2

Views: 110

Answers (4)

Dylan Su
Dylan Su

Reputation: 6065

Here is a solution by using user variables.

SQL:

-- Prepare data, just for demo
create table table1(Places varchar(200));
create table table2(Distant varchar(200));

insert into table1 values
('Cupertino'),
("BJ's Restaurant"),
('Cupertino Inn'),
('Outback Steakhouse');
insert into table2 values
('0.6 km'),
('1.3 km'),
('1.3 km'),
('1.1 km');
select * from table1;
select * from table2;

-- Process of combination
SET @rownum1:=0; 
SET @rownum2:=0; 
SELECT t1.Places, t2.Distant
FROM 
    (SELECT *, @rownum1:=@rownum1+1 AS rownum FROM table1) t1, 
    (SELECT *, @rownum2:=@rownum2+1 AS rownum FROM table2) t2
WHERE t1.rownum = t2.rownum ;

Output:

mysql> select * from table1;
+--------------------+
| Places             |
+--------------------+
| Cupertino          |
| BJ's Restaurant    |
| Cupertino Inn      |
| Outback Steakhouse |
+--------------------+
4 rows in set (0.00 sec)

mysql> select * from table2;
+---------+
| Distant |
+---------+
| 0.6 km  |
| 1.3 km  |
| 1.3 km  |
| 1.1 km  |
+---------+
4 rows in set (0.00 sec)

mysql>
mysql> -- Process of reporting
mysql> SET @rownum1:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @rownum2:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT t1.Places, t2.Distant
    -> FROM
    ->     (SELECT *, @rownum1:=@rownum1+1 AS rownum FROM table1) t1,
    ->     (SELECT *, @rownum2:=@rownum2+1 AS rownum FROM table2) t2
    -> WHERE t1.rownum = t2.rownum ;
+--------------------+---------+
| Places             | Distant |
+--------------------+---------+
| Cupertino          | 0.6 km  |
| BJ's Restaurant    | 1.3 km  |
| Cupertino Inn      | 1.3 km  |
| Outback Steakhouse | 1.1 km  |
+--------------------+---------+
4 rows in set (0.00 sec)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

The problem is that you have no way to "correspond" the values in the two tables, because SQL tables represent unordered sets. If you are loading the data into the tables from an external source, then include an auto_increment column to get the original ordering.

Lacking that, you can get something like what you want, by enumerating the rows in each table and joining/aggregating on that. This comes close to what you want:

select max(col1) as col1, max(col2) as col2
from ((select t1.col as col1, NULL as col2, (@rn1 := @rn1 + 1) as seqnum
       from table1 t1 cross join (select @rn1 := 0) params
      ) union all
      (select NULL, t2.col, (@rn2 := @rn2 + 1) as seqnum
       from table2 t2 cross join (select @rn2 := 0) params
      )
     ) tt
group by seqnum;

This does not guarantee the original ordering in the tables, but it will produce a new table combining exactly one row from each of the other two tables.

Upvotes: 4

hegu_141
hegu_141

Reputation: 64

If you add some id's then you can do a join:

SELECT A.field, A.otherfield, B.field, B.otherfield, C.yetanotherfield
FROM table_A AS A
JOIN table_B as B on A.id = B.a_id
JOIN table_C as C on B.id = C.b_id

Upvotes: 1

mi_h
mi_h

Reputation: 54

in table2 you need to have a column to identify which entry of table1 belongs to the entry in table2, so a foreign key is needed.

Upvotes: 1

Related Questions