CODEWITHSUNDEEP
phpmysql
Hopewell Mutanda
Hopewell Mutanda

Reputation: 1075

Return all rows matching a certain id in a single result in mysql

I have a weather database with three tables: Locations, WeekDays and Weather and they have the following columns

Locations
location_id | location_name

Weekdays
day_id | day_name

Weather
location_id | day_id | max_temp | min_temp | avg_temp | humidity | rainfall | windspeed

location_id and day_id are foreign keys. Now I need to return all weather elements of a certain location for the whole week Sunday - Monday in a single result. I am sure I'm supposed to use keywords like AS 'monday_max_temp', AS 'tuesday_rainfall' etc. The results should be 7(days)* the number of weather elements i have in the database for each location. Can someone help me with the kind of sql query I can use to achieve this?

Upvotes: 0

Views: 144

Answers (2)

Eddy
Eddy

Reputation: 235

For starters:

SELECT
Locations.location_id, Locations.location_name,
Weekdays.day_id, Weekdays.day_name,
Weather.*
FROM Locations
CROSS JOIN Weekdays
LEFT JOIN Weather
ON Weekdays.day_id = Weather.day_id AND Locations.location_id = Weather.location_id

Can you explain a little more about the 'keywords'. What do you mean/want with them?

Why do you want to exclude the location? You don't want seven days for each location?

I've seen your reply and I will show you the SQL. You can do what you ask in the SLQ, but there will be not much programmers that will choose this approach. It is usily better to make the crosstab in the presentation.

SELECT
    Locations.location_id, Locations.location_name,
    WeatherMonday.max_temp AS monday_max_temp, WeatherMonday.min_temp AS monday_min_temp, ... ,
    WeatherTuesday .max_temp AS tues_max_temp, ... ,
    ... ,
FROM Locations
INNER JOIN Weather AS WeatherMonday ON WeatherMonday.day_id = 1 AND Locations.location_id = WeatherMonday.location_id
INNER JOIN Weather AS WeatherTuesday ON WeatherTuesday.day_id = 2 AND Locations.location_id = WeatherTuesday.location_id
...

Upvotes: 1

Matej Žvan
Matej Žvan

Reputation: 766

What about:

SELECT * FROM Weather WHERE location_id = "desired id" order by day_id desc limit 7;

Asuming your day_id is auto increment field and you add a row for each day. You will get last 7 rows for desired location orderd from last insert to first.

Upvotes: 0

Related Questions