SQL joins across many tables on postgresql

Question

I have a table structure as follows:

measurements: id | Value | sensor_id

Now I have another table called sensors where

sensors: id | Name | provider_id

Then I have another table called Providers as:

providers: id | Name

Now what I want to do is select all the records from the measurements table where the sensor_ID has a particular type (specified by an ID).

So I did something like:

select * from measurements, sensors, providers JOIN sensors on sensors.id = measurements.sensor_id JOIN providers on providers.id = sensor.provider_id where provider.id = 1 LIMIT 100

However, this comes back with There is an entry for table "sensor_measurements", but it cannot be referenced from this part of the query. I have tried other kind of variants on this but could not get it to work.

I am using Postgresql and using the PgAdmin tool.

Gordon Linoff · Accepted Answer · 2018-11-21 12:07:41Z

Never use commas in the FROM clause. So:

select * -- you should list out the columns you want from measurements m join sensors s on s.id = m.sensor_id join providers p on p.id = s.provider_id where p.id = 1 limit 100

TommyN · Accepted Answer · 2018-11-21 12:21:43Z

It seems to me that your FROM clause is wrong. It should either be:

FROM measurements JOIN sensors on sensors.id = measurements.sensor_id JOIN providers on providers.id = sensor.provider_id

or alternatively

FROM measurements, sensors, providers WHERE providers.id = 1 and sensors.provider_id = providers.id measurements.sensor_id = sensors.id

You're currently including the 3 tables you want but in addition you're joining in the sensors and providers tables yet again, giving you a total of 5 (as far as I can read it).

Collectives™ on Stack Overflow

SQL joins across many tables on postgresql

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related