CODEWITHSUNDEEP
sqlsqlite
b-ryce
b-ryce

Reputation: 5828

Combining multiple SELECT statements in one query

I have a table called "Locations" that looks like this:

   ID   lat     lng
0   0   -121    41
1   1   -122    42
2   2   -123    43
3   3   -124    44

And another table called "Segments" that looks like this:

   ID    StartID  EndID
0   0    0        1
1   1    0        3
2   2    2        3
3   3    3        1

The start and end IDs refer to IDs in the "Locations" table

I want a single query that pulls together the lat and lng values from the Locations table for the start and end ID and returns a table that looks like this:

   ID    StartID  StartLat  StartLng  EndID  EndLat  EndLng
0   0    0        -121      41        1      -122    42
1   1    0        -121      41        3      -124    44
2   2    2        -123      43        3      -124    44
3   3    3        -124      44        1      -122    42

Here is the query I'm trying:

SELECT 
    lat as StartLat, lng as StartLng
FROM Locations
WHERE ID = (
    SELECT ID as StartID
    FROM Segments
)
UNION
SELECT 
    lat as EndLat, lng as EndLng
FROM Locations
WHERE ID = (
    SELECT ID as EndID
    FROM Segments
)

But this is only giving me the the StartID, StartLat and StartLng, not combining them into one table like I expect with the Union statement. What am I missing? Thanks for the assistance!!

Upvotes: 0

Views: 50

Answers (2)

ravioli
ravioli

Reputation: 3833

It looks like you just need a couple LEFT JOINS:

SELECT 
  s.ID, 
  s.StartID, ls.lat AS StartLat, ls.lng AS StartLng, 
  s.EndID, le.lat AS EndLat, le.lng AS EndLng 
FROM Segments s
LEFT JOIN Locations ls ON s.StartID = ls.ID -- Start info
LEFT JOIN Locations le ON s.EndID = le.ID -- End info

Upvotes: 1

xdazz
xdazz

Reputation: 160843

You need join instead of Union.

  select
    s.*,
    startLocation.lat AS StartLat,
    startLocation.lng AS StartLng,
    endLocation.lat AS EndLat,
    endLocation.lng AS EndLng
  from
    Segments s
    left join Locations startLocation on s.startID = startLocation.ID
    left join Locations endLocation on s.EndID = endLocation.ID

Upvotes: 1

Related Questions