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

Question

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.

Popeye · Accepted Answer · 2019-10-18 12:14:07Z

2

You can use cross join .

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

Cheers!!

answered Oct 18, 2019 at 12:14

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marcus Over a year ago

just want to know what is the difference between your answer and answer from @Gordon Linoff ? Why duplicate answer ?

Popeye Over a year ago

@andrew Difference is I posted the answer and gordon posted after me . The gap was 11 seconds.

Gordon Linoff · Accepted Answer · 2019-10-18 12:14:19Z

1

You seem to want a cross join:

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

answered Oct 18, 2019 at 12:14

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Comments

Marcus · Accepted Answer · 2019-10-18 12:18:14Z

0

Try below:

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

answered Oct 18, 2019 at 12:18

Marcus

4,02130 gold badges78 silver badges125 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related