CODEWITHSUNDEEP
sqlsql-servert-sql
Rafael Santos
Rafael Santos

Reputation: 21

How to create a table based on another 2 Tables?

So I have two tables, imagine:

And I want a third table that has all the cases between these two tables:

+--------+---------+
|  City  |   Name  |
+--------+---------+
| Paris  |  John   |
| Paris  |  Mary   |
| Paris  |  Peter  |
| London |  John   |
| London |  Mary   |
| London |  Peter  |
+--------+---------+

Is it possible to do this? I can't do it with a join because they aren't related in any way.

Upvotes: 2

Views: 98

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

I can't do it with a join because they aren't related in any way.

You actually could do this with a join:

select c.city, n.name
from table2 c join
     table1 n
     on 1 = 1;

The condition in the on clause can be almost anything.

However, Cartesian products -- what you want to do -- are the basis for understanding joins. So, SQL supports them directly:

select c.city, n.name
into table3
from table2 c cross join
     table1 n;

Actually writing this as an explicit join is not as crazy as it sounds. If either table is empty, then the cross join returns no rows. Sometimes, you want to keep rows in the other table. Outer joins solve this problem:

select c.city, n.name
into table3
from table2 c full join
     table1 n
     on 1 = 1;

Upvotes: 1

sanjay sisodiya
sanjay sisodiya

Reputation: 465

Yes, it is possible. Please use below SQL QUERY.

This is SQL:

create table table3 AS
select table1.Name,table2.City from table1 
join table2;

I hope using the above SQL your problem will be resolved.

Upvotes: 0

DarkRob
DarkRob

Reputation: 3833

If you want to create third table then

Use this:

  Select * into Table3 from ( select City, Name from Name cross join City  ) as Tab

  GO

Else you may create a view for same.

 Create View NameCity 
 As
 select City, Name from Name cross join City

 GO

Upvotes: 0

Related Questions