Table Aliases: MySQL says table R1 does not exist when Joining the same table 3 times

Question

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>");
?>

score 1 · Accepted Answer · answered Oct 08 '22 at 07:51

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;

score 0 · Answer 2 · answered Oct 08 '22 at 04:36

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?

Table Aliases: MySQL says table R1 does not exist when Joining the same table 3 times

2 Answers2