1

I have a table R with the following fields stated below:

weatherID, 
dateRecorded, 
Days, 
locationID, 
timeRecorded, 
rainAmount, 
rainNumberHours, 
temperature, 
windSpeed, 
windDirection

NB: The Days field is an integer type that describes the number of days with auto-increment +1. The weatherID field is a concatenation of two other fields in two different tables not shown here.

This schema of the table is simplified, but it is relevant to what I want to investigate. We want to find out if there exist some weather patterns that follow other weather patterns and to find records that satisfy specific parameters and conditions.

I use aliases in the query to get records for different field values Days.

A model query will make it clear: I am joining the table R, multiple times in an alias style query so that I have alias R1 and alias R2 making it look like joining three tables together.

$sql = "
SELECT R.weatherID, R.Days, R.locationID, R.timeRecorded, R.windDirection, 
R1.weatherID, R1.Days, R1.locationID, R1.timeRecorded, R1.windDirection, 
R2.weatherID, R2.Days, R2.locationID, R2.timeRecorded, R2.windDirection 
FROM ((R 
INNER JOIN R1 ON R.Days= R1.Days) 
INNER JOIN R2 ON R.locationID= R2.locationID) 
WHERE ((R.Days)=[R1].[Days]-2) AND ((R1.Days)=[R2].[Days]-2);
"

This model gives me similar weather patterns that re-occurs or repeat within two (2) days (refers to the "2" in the WHERE clause).

Now the question is how do we

I want to arrange the results of the query in a grid-like manner in the following way:

R.weatherID R.Days R.locationID R.windDirection R1.weatherID R1.Days R1.locationID R1.windDirection R2.weatherID R2.Days R2.locationID R2.windDirection

And the next record is also in the same grid-like format.

I have only table R in the database: that is I have weatherdb.R, but I don't have "weatherdb.R1" and "weatherdb.R2" in other words, I don't have tables R1 and R2 but am using them in aliases, which is not working.

But, MySQL said: Documentation #1146 - Table 'weatherdb.R1' doesn't exist

The sqlWeather1.php file is my first trial example below:

<?php
    require "database/connection.php";

$sql="SELECT R.weatherID, R.Days, R.locationID, R.timeRecorded, R.windDirection, 
R1.weatherID, R1.Days, R1.locationID, R1.timeRecorded, R1.windDirection, 
R2.weatherID, R2.Days, R2.locationID, R2.timeRecorded, R2.windDirection 
FROM ((R INNER JOIN R1 ON R.Days = R1.Days) INNER JOIN R2 ON R.locationID = R2.locationID) 
WHERE ((R.Days)=R1.Days-2) AND ((R1.Days)=R2.Days-2)
ORDER BY R.Days DESC;";

$result=mysqli_query($db, $sql) or die("Bad query:$sql");

//$result = R2($query);
echo("<table border = 1>");
$first_row = true;
while ($row = mysqli_fetch_assoc($result)) {
    if ($first_row) {
        $first_row = false;
        // Output header row from keys.
        echo '<tr>';
        foreach($row as $key => $field) {
            echo '<th>' . htmlspecialchars($key) . '</th>';
        }
        echo '</tr>';
    }
    echo '<tr>';
    foreach($row as $key => $field) {
        echo '<td>' . htmlspecialchars($field) . '</td>';
    }
    echo '</tr>';
}
echo("</table>");
?>
0

2 Answers 2

1

I see two problems in the given query.

I am joining the table R, multiple times in an alias style query so that I have alias R1 and alias R2 making it look like joining three tables together

You forgot to assign the alias R1 , R2 names on the R table

Change ,

INNER JOIN R1 ON R.Days= R1.Days
INNER JOIN R2 ON R.locationID= R2.locationID

to

INNER JOIN R as R1 ON R.Days= R1.Days
INNER JOIN R as R2 ON R.locationID= R2.locationID

Square brackets aren't allowed in MySQL. I'm not sure but [] are pretty much unique to T-SQL , so remove them and the irrelevant parentheses .

Your final query would look like:

SELECT R.weatherID, 
       R.Days, 
       R.locationID, 
       R.timeRecorded, 
       R.windDirection, 
       R1.weatherID, 
       R1.Days, 
       R1.locationID, 
       R1.timeRecorded, 
       R1.windDirection, 
       R2.weatherID, 
       R2.Days, 
       R2.locationID, 
       R2.timeRecorded, 
       R2.windDirection 
FROM  R 
INNER JOIN R as R1 ON R.Days= R1.Days
INNER JOIN R as R2 ON R.locationID= R2.locationID 
WHERE R.Days=R1.Days-2 
AND R1.Days=R2.Days-2;
1
  • 3rd problem: ON R.Days = R1.Days and WHERE R.Days = R1.Days-2. They probably meant to have ON R.locationID = R2.locationID Oct 8, 2022 at 17:21
0

These contradict each other, so you should get an empty result set:

ON       R.Days = R1.Days)
WHERE  ((R.Days)=[R1].[Days]-2)

Get rid of the parentheses in the FROM and JOINs. They seem irrelevant and possibly cause the error you encountered.

What's with the square brackets?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.