CODEWITHSUNDEEP
sqloracle-database
Big Rick
Big Rick

Reputation: 166

SQL: joining a single row from one table to all rows in another table

I'm trying to calculate the distance between each town in relation to some locations.

I have a TOWN table with multiple towns as:

Town No | Latitude | Longitude |
Town 1       | -20      | 100       |
Town 2       | -30      | 120       |

I have a LOCATION table with multiple locations as:

Loc No  | Latitude | Longitude |
Loc 1       | -25      | 150       |
Loc 2       | -30      | 150       |
Loc 3       | -18      | 120       |

Basically what I want is to combine these tables such that:

Town No | Latitude | Longitude | Loc No | Latitude | Longitude |
Town 1       | -20      | 100       | Loc 1      | -25      | 150       |
Town 1       | -20      | 100       | Loc 2      | -30      | 150       |
Town 1       | -20      | 100       | Loc 3      | -18      | 120       |
Town 2       | -30      | 120       | Loc 1      | -25      | 150       |
... and so on

I've selected the data from the town and location tables but in two separate queries. I can't figure out how to write this in one query to get the desired output - I keep getting an error message say that a single row subquery returns more than one row. I'm stuck here - any help would be appreciated. Apologies if this doesn't make sense, please let me know and I'll provide more details.

Upvotes: 0

Views: 193

Answers (3)

Marcus
Marcus

Reputation: 3869

Try below:

select t1.*, t2.*
from town t1 join
     location t2
     on 1 = 1;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You seem to want a cross join:

select t.*, l.*
from town t cross join
     location l;

Upvotes: 1

Popeye
Popeye

Reputation: 35900

You can use cross join .

Select t1.*, t2.*
From town t1 cross join location t2

Cheers!!

Upvotes: 2

Related Questions